Help With Table Design

[mattv10]

Hi,

I'm currently in an internship working solely with databases. I've become really interested in designing databases and I now want to design my own. I was wondering if I explained my table structure then someone could give me their thoughts and ideas for improvement. I have a huge sport trading card collection and I wanted to create a database for it. This way I could run some quick queries to find out how much my collection could be worth, how many cards I have, easily keep track of duplicates. Things like that.. This might seem strange, but I think it would be a good learning experience that would help me utilize many of the features of Access.

My Table Structure:

Ok I have a Company table(Topps, UpperDeck, etc) and they sell many boxsets(which would be another table).. Boxsets have many Cards(which would be my third table)...

Now here is where I get confused. I want have a sports table that would categorize my cards by what sport they are, but I don't know where I could link this. I also have a players table. This table will have a 1:M with Cards because 1 player can have many cards, but couldn't Cards and Players have a 1:1 because 1 card shows one player???

Finally, I want a Price Table that will be linked somewhere, but I've yet to figure out where to link this too.. Have I missed anything???

Your ideas and opinions are greatly appreciated!!!

Thanks!

 

[cuttsy]

It seems correct that the relationship between players and cards is a 1-* that is because a player can have many cards but a card can only have one player.

You mention the fact that one card can only show one player. This is the '1' side of the 1-* relationship.

But the fact that one player can be shown on many cards represents many side of the relationship.

I'm unsure of what you mean by a 'price table'. I am not sure that you need one. I would say that you would possibly have a price field in your card table.

In terms of sports category. Companys make cards for different sports so you can't do it there. However if a boxset is guaranteed to always contain cards for only one sport you could have a lookup field in your boxset table. This is a bit risky as it requires predicting the future.

You could have the category lookup field in the card table. This has a greater data overhead in terms of size and dataentry time but it may be necessary.

You could have a Category table and a BoxSet_Categorys table which would have (BoxSetID, Category) This would allow a box set to have multiple categorys. I think this is overkill.

Other than that your design looks sound on the face of it. Unless you can have cards that don't belong to a box set. Then you have problems.