question

Eric Watts avatar image
Eric Watts asked

Trading Card Database Design

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

databasemysqldatabase-design
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Scot Hauder avatar image
Scot Hauder answered

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

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.