|
I am working on creating a sports card database. I am trying to figure out the best way to layout the tables. Here is the data I am working with. Each card belongs to a set of cards and a sport Each set of cards belongs to a brand of cards Each brand of cards belongs to a manufacturer That part I thought I had all worked out, until I thought about years. Not all manufacturers existed throughout all the years, not all brands were produced by the manufacturers during all of the years, and not every set was produced under its brand every year. So how should I include the year into the database while reducing redundancy? Any design strategies would greatly be appreciated. Eric Watts
(comments are locked)
|
|
You could use StartDate and EndDate instead of adding rows for every year. When you want to show all years a certain brand was manufactured by a certain manufacturer you can use a tally/numbers table technique to produce the years. E.g. your ManufacturerBrand join-table could look like this: ManufacturerID int, BrandID int, ProductionStartDate Date, ProductionEndDate Date What if there are gaps in the years. For instance a manufacturer manufactured a certain brand from 56-64 then stopped, then began again from 68-present?
May 30 '10 at 08:55 PM
Eric Watts
You would have multiple rows in the table, one for each Manufacturer/Brand/date range. Make sure the ranges do not overlap for the Manufacturer/Brand. When joining the Card table you will want to add WHERE Card.Date BETWEEN ManufacturerBrand.ProductionStartDate AND ManufacturerBrand.ProductionEndDate
May 30 '10 at 11:51 PM
Scot Hauder
(comments are locked)
|
