Search code examples
databasedatabase-designnormalizationdatabase-normalization

Normalizing a Table 6

I'm putting together a database that I need to normalize and I've run into an issue that I don't really know how to handle.

I've put together a simplified example of my problem to illustrate it:

Item ID___Mass___Procurement__Currency__________Amount
0__________2kg___inherited____null________________null
1_________13kg___bought_______US dollars_________47.20  
2__________5kg___bought_______British Pounds______3.10
3_________11kg___inherited____null________________null   
4__________9kg___bought_______US dollars__________1.32

(My apologies for the awkward table; new users aren't allowed to paste images)

In the table above I have a property (Amount) which is functionally dependent on the Item ID (I think), but which does not exist for every Item ID (since inherited items have no monetary cost). I'm relatively new to databases, but I can't find a similar issue to this addressed in any beginner tutorials or literature. Any help would be appreciated.

Solution

  • Not only Amount, everything is dependent on ItemID, as this seems to be a candidate key.

    The dependence you have is that Currency and Amount are NULL (I guess this means Unknown/Invalid) when the Procurement is 'inherited' (or 0 cost as pointed by @XIVsolutions and as you mention "inherited items have no monetary cost")

    In other words, iems are divided into two types (of procurements) and items of one of the two types do not have all attributes.

    This can be solved with a supertype/subtype split. You have a supertype table (Item) and two subtype tables (ItemBought and ItemInherited), where each one of them has a 1::0..1 relationship with the supertype table. The attributes common to all items will be in the supertype table and every other attribute in the respecting subtype table:

    Item
----------------------------
ItemID    Mass   Procurement
0          2kg   inherited
1         13kg   bought
2          5kg   bought
3         11kg   inherited 
4          9kg   bought

ItemBought
---------------------------------
ItemID    Currency         Amount
1         US dollars       47.20  
2         British Pounds    3.10 
4         US dollars        1.32

ItemInherited
-------------
ItemID
0       
3

    If there is no attribute that only inherited items have, you even skip the ItemInherited table altogether.

    For other questions relating to this pattern, look up the tag: Class-Table-Inheritance. While you're at it, look up Shared-Primary-Key as well. For a more concpetual treatment, google on "ER Specialization".