x

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

more ▼

asked May 30 '10 at 04:37 AM in Default

Eric Watts gravatar image

Eric Watts
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest

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

more ▼

answered May 30 '10 at 04:32 PM

Scot Hauder gravatar image

Scot Hauder
5.9k 13 15 18

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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x235
x107
x66

asked: May 30 '10 at 04:37 AM

Seen: 1343 times

Last Updated: May 30 '10 at 04:37 AM