Help with Database Design

Question

I have four tables: ItemCategory, Items, DiamondCategoy and Diamonds.

The users store item details and specify whether it has a diamond on, for example:

ItemCategory: Ring
Item: R1

If there is a diamond then:

DiamondCategory: Round
Diamond: D1

So R1 of Ring has D1 of Round

An Item could have no diamonds, for example:

ItemCategory: Ring
Item: R1
DiamondCategory: None
Diamond: None

I can't figure out how to design the relationships. I came up with this solution, correct me if I'm wrong.

ItemCategory:

c_Id >> PK

Items:

p_Id >> PK

c_Id >> FK

d_Id >> FK

Diamonds:

d_Id >> PK

dc_Id >> FK

DiamondCategory:

dc_Id >> PK

Is that correct?.

Answer 1

This looks fine to me, as far as it goes. If an Item can have more than one type of Diamond (as many rings do) you would probably want to have an intersection (junction) table, like this:

Items:

p_Id >> PK

c_Id >> FK

ItemDiamonds

d_Id >> FK  >>
            >>  UK or PK
p_id >> FK  >>

no_of_diamonds 

So Item ID and Diamond ID are individually foreign keys, and together (Item ID, Diamond ID) form a primary or unique key. The no_of_diamonds attribute assumes an Item can have more than one of any type of Diamond.

Having a separate table for each look-up code (your category tables) has two advantages:

• It is the only way to enforce the foreign key for the appropriate Category ID to a given Category
• Database products with sophisticated optimizers (such as Oracle) can make use of the more specific tables to produce more accurate execution plans.

Answer 2

If an Item and a Diamond can only have one category, why do you need separate tables for those? Just include the category as an attribute on your Item and Diamond tables.

Then you'd have two tables, one for Items and one for Diamond, and a third table which serves as a lookup table between the two, and would store the primary key for the Item and the primary key for the corresponding diamond that it has.