[ericwatts]

I am attempting to create a database to store my Sports Card Collection. I am fairly new at MySQL, however I do have basic database structure knowledge. Attached is my ER Diagram for what I have laid out so far. I am just looking for some constructive criticism, to help prevent issues before I start working on the front end.

Some things I am looking at:

• Manufacturers could make cards for more than one sport
  
• Manufacturers may not have produced brands consistently from start year to end year
  
• I want to be able to have cascading combo boxes on my front end to streamline card input. I want to be able to select any combo box to start with whether it be Year, Sport, Manufacture, brand, etc.
  
• I was originally going to incorporate a Player table, however some cards have more than one player. Wasn't sure how to circumvent that.

Any help on solidifying my design would be of great help.

Attached File(s)

•  Card DB.pdf (87.75K)
  Number of downloads: 147

[Atli]

Hey.

One thing I notice, is that you put the ID of every table in the "chain" directly into the Card table, where you could just put the last table in the chain and then follow it in a query.

The "SubSets" table contains a reference to the "Sets" table, which references the "Brands" table, which references the "Sport_Manufacturers" table, which references the "Sports" table, which is then finally references by the "Teams" table. -- So by putting a "SubSets_ID" in the "Cards" table, you can follow it down the line all the way to the "Teams" table.

I would consider removing the links to those tables from the Cards table. You could then have your queries join them to get the same results.
For example, consider this alteration to your ERD:


You should be able to replicate the original table in your post using a query, which you could turn into a view so it wouldn't have to be called every time. (It's rather verbose.)

CREATE VIEW CardInfo AS
SELECT *
FROM Cards AS c
LEFT JOIN SubSets AS ss
    ON ss.SubSets_ID = Cards.SubSets_ID
LEFT JOIN Sets AS s
    ON s.Sets_ID = ss.Sets_ID
LEFT JOIN Brands AS br
    ON br.Brands_ID = s.Brands_ID
LEFT JOIN Sports_Manufacturers AS sm
    ON sm.Sports_Manufacturers_ID = br.Sports_Manufacturers_ID
LEFT JOIN Manufacturers AS m
    ON m.Manufacturers_ID = sm.Manufacturers_ID
LEFT JOIN Sports AS sp
    ON sm.Sports_ID = sp.Sports_ID
LEFT JOIN Teams AS t
    on t.Ports_ID = sp.Sports_ID

Which would allow you to just do this to get the same original structure in your design.

SELECT * FROM CardInfo

I may of course be going a bit for to keep the design clean (I always do that ).
You might find your original design better suited for practical use, as it is undoubtedly easier on the server's CPU (not having to execute all those joins), but this is worth considering.