create table based on sequential data-one title, many issue numbers for each

  • Thread starter Thread starter angarahad
  • Start date Start date
A

angarahad

Guest
so here's my situation--
i'm creating a db based on part of my comic book
collection. the main data values will be
comic titles, volume #'s and issue #'s,
the publishers (marvel, dc, dark horse, crossgen, & image--
i won't be breaking these down into their individual
imprints--e.g. top cow or wildstorm).

my big problem is that each title has its series of issue
numbers--so it's not like when you're dealing with books
where you have the one title and the one volume number to
deal with. i know i'm supposed to avoid having repeating
values in the tables--so how can i designate the volume
number and the sequential issue number for each title--
without repeating the title's name-value for each issue
number? wouldn't each individual issue need to have its
own record in the table?

here's the data values listed by field name below--

data group I--
Comic ID/Comic Title/Volume#/Issue#

data group II-
Publisher ID/ Publisher Name

(possible) data group III-
Creator ID/
Writer First Name/Writer Last Name
Artist First Name/Artist Last Name
Inker First Name/Inker Last Name
Colorist First Name/Colorist Last Name


and as far as data group III--is it even possible to have
a table for the creators' info--since there are various
scenarios that occur that will lead to repeating values
again. sometimes the writer, artist and colorist are all
one person. for each issue of a comic, the writer may be
the same person for a number of issues, then he may be
replaced by someone else after a 7 issue run, and the
replacement's run might last for 5 or more issues. i'm
thinking i might be better off not even bothering with the
creator data--and just focus on the comic titles and
publishers.

thanks in advance for any thoughts and advice.
 
i know i'm supposed to avoid having repeating
values in the tables--so how can i designate the volume
number and the sequential issue number for each title--
without repeating the title's name-value for each issue
number
The way you do this is by creating additional tables. The following should get you started. You'll need to create relationships between the related tables and select the enforce referential integrity option. You'll need to learn how to create queries to pull all the data back together for forms and reports. Take a look at northwind.mdb A lot of what you need to learn can be learned from that db.

Just remember, anytime you have more than one of something, you can have many and so you need to create a separate table to hold the data.

tblTitles:
ComicID (autonumber primary key)
ComicTitle
PublisherID (foreign key to tblPublisher)
etc. (anything else that occurs only once per title)

tblVolume:
VolumeID (autonumber primary key)
ComicID (foreign key to tblTitles)
VolumeNum
etc. (anything else that occurs only once per volume)

tblIssue:
IssueID (autonumber primary key)
VolumeID (foreign key to tblVolume)
IssueNum
etc. (anything else that occurs only once per issue)

tblContributors:
ContributorID (autonumber primary key)
FirstName
LastName
etc. (anything else about this person)

tblPublisher:
PublisherID (autonumber primary key)
PublisherName
etc. (anything else about this publisher)

tblTalent:
TalentID (autonumber primary key)
TalentName (Writer, Artist, Inker, or Colorist)

tblIssueContribution:
ContributorID (foreign key to tblContributor)
IssueID (foreign key to tblIssue)
TalentID (foreign key to tblTalent)
 
thank you!

the examples you've given me will work out perfectly--i guess i just needed another point of view to help me see the "way ahead" more clearly. now i have a "blueprint" to work from!
 

Users who are viewing this thread

Back
Top Bottom