Sports Collection Database

Question

I am creating a sports collection database of different sports and i am confused of some sort on what tables and primary keys/foreign key combo to use the requirement is as follows:

Should be in a 3rd Normal Form or at least in 3NF.

Here is the Full Description on what I want to do:

1. Design a database for a sports collection of cards.

2. You only purchase cards with current market value( at the time of purchase) that are at least $100.00. Your collection about 1000 cards

3. You collect all kinds of sports cards, from football, basketball, hockey and etc

4. These cards you collect are produced by different vendors such as ?Topps, Upper Deck, Leaf and Panini.

5. The collection spans many years of collection, some cards even date back to early 1900s, but there are cards made even before this

6. The condition of your cards vary too and are grade according to the 10-point PSA grading standards. (Grading table)

7. Everytime you purchase a card, you must know the date of purchase, cost of purchase, the market value of that purchase, whom you purchased it from, sport, the individual on the cards, card number and etc.

8. After completion of purchase, you send the card out for grading about a week later, you want to be able to keep track on when the card was sent out for grading, status of the grading, graded assigned to the card , when it was return by the grading company and receipt of the returned card. for tax purposes, you also want to know the fee paid for the grading service (grade table)

9. you do buy and sell cards, so you also want to know how much you sold the card for for, when it was sold, to whom it was sold, shipping fee if applicable and any relevant data pertinent to sale transaction. You are free to include any additional data that you believe is important to the table.

Here is my current database so far... I feel like i am missing something and I did not follow the requirements properly

Answer 1

It looks like a pretty good start. I have the following observations about your tables/structure:

CardSellers: should only store info about the CardSeller; the only values it should have are SellerID and SellerName

Vendor: should not have CardId

Grade: SellerID doesn't belong here

Card: looks good

CardBuyers should not have DateOfPurchase or CostOfCard or Shipping Fee; those three members belong in CardTransaction. In fact, "DateOfPurchase" should be "DateOfTransaction"

Also, since the Vendor table should only store Vendor data, and the Card table should only store card data, you also need a M2M (many-to-many) table to connect the two; this should have three members: ID (PK), CardId (FK), and VendorId (FK)

UPDATE

Remember two things about table design:

(0) A table should only contain data about the object its named for and only references to other tables (via a FK - see below)
(1) DRY (Don't Repeat Yourself - IOW, don't store the same data in multiple places; instead, store a link to it, where needed, via FK (Foreign Key) fields referencing PK (Primary Key) fields).

As far as the actual design of the tables, this makes sense to me:

CARDS
-----
CardId (PK)
VendorId (FK)
CardFirstName
CardLastName
CardType
CardYear
MarketValue
Rarity
CollectionNumber    

COLLECTORS (Buyers and/or Sellers; no need to have separate tables for them)
---------
CollectorId (PK)
CollectorName
CollectorAddress    

TRANSACTIONS
------------
TransId (PK)
CardId (FK)
BuyerId (FK) <= CollectorId in the Collectors table
SellerId (FK) <= CollectorId in the Collectors table
TransactionDate
Price (if it may differ from CARDS.MarketValue)

GRADE
-----
GradeId (PK)
CardId (FK)
Points
AssignedGrade
Qualifiers
Status
CardFee
GradedDate
ReturnedDate

VENDORSLU ("LU" stands for "Lookup")
---------
VendorId (PK)
VendorName
CARDTYPESLU
-----------
CardTypeID (PK)
CardTypeDescription

RARITYLU
-----------
RarityID (PK)
RarityDescription

You may find there are other fields you need to add, too, but that should be close. You might even want additional lookup tables, such as for "Qualifiers" and "Status" in the GradeTable.

You might also want a SPORTSLU table, and then add a SportId FK to the CARDS table (where if SportId == 1, it's a football card, if it's 2, it's a basketball card, &c).

Note, though, that as prevalent and historically significant as Relational Databases are, there is another animal called "Non-SQL" databases (such as MongoDB) which are more free-form/loosey-goosey/I'm okay-you're okay/anarchistic, which allow you to have records, or "documents" that can omit or add whatever members it wants on the fly. Relational Databases can be compared to Classical music (Bach, Mozart, &c) whereas Non-SQL are more like Jazz (free-flowing, improvisational).

And BTW, why no mention of Tiddlywinks? Football, Baseball, etc., are fine, but no Tiddlywinks (or Bocci Ball, for that matter)?!?