Normalization of table 6

Question

I am building a database that I need to normalize, and I ran into a problem that I really don't know how to handle.

I put together a simplified example of my problem to illustrate this:

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 inconvenient table, new users are not allowed to insert images)

In the above table, I have a (Amount) property that is functionally dependent on the Item Item (I think), but which does not exist for each Item ID (since the inherited items have no cash costs). I am relatively new to databases, but I cannot find a similar problem for this in any beginner's tutorial or literature. Any help would be appreciated.

Answer 1

Not only Amount , it all depends on the ItemID , as it seems to be the candidate key.

You have a Currency and Amount NULL dependency (I assume it means Unknown / Invalid) when Procurement is equal to 'inherited' (or 0 value specified as @XIVsolutions, and as you mention “inherited items have no cash costs”)

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

This can be solved by separating the supertype / subtype. You have a supertype table ( Item ) and two subtype tables ( ItemBought and ItemInherited ), where each of them has a 1::0..1 relationship to the supertype table. Attributes common to all elements will be in the supertype table and in every other attribute in the correspondence 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 has only inherited elements, you even skip the ItemInherited table ItemInherited .

For other questions related to this template, find the tag: Class-Table-Inheritance. While you are on it, also look at the Shared-Primary-Key. For more constant reference, google on "ER Specialization".

Answer 2

I would just create two new ItemProcurement and Currencies tables.

If I’m not mistaken, then according to the data presented, the amount is part of the purchase of the item itself (when the item was not inherited), so I would group Amount and CurrencyID in a new ItemProcurement object.

As you can see, the inherited item will not have entries in the ItemProcurement table.

As for the main table Item , if you expect only two different values ​​for the type of purchases, then I would use the column char(1) (from B => bougth, I => inherited).

I would look like this:

Then the data will look like this:

 TABLE Items +-------+-------+--------------------+ | ID | Mass | ProcurementMethod | |-------+-------+--------------------+ | 0 | 2 | I | +-------+-------+--------------------+ | 1 | 13 | B | +-------+-------+--------------------+ | 2 | 5 | B | +-------+-------+--------------------+ TABLE ItemProcurement +--------+-------------+------------+ | ItemID | CurrencyID | Amount | |--------+-------------+------------+ | 1 | 840 | 47.20 | +--------+-------------+------------+ | 2 | 826 | 3.10 | +--------+-------------+------------+ TABLE Currencies +------------+---------+-----------------+ | CurrencyID | ISOCode | Description | |------------+---------+-----------------+ | 840 | USD | US dollars | +------------+---------+-----------------+ | 826 | GBP | British Pounds | +------------+---------+-----------------+ 

Answer 3

Here is my hint:

UPDATE: Mass will be Float / Decimal / Double depending on your Db, the cost will be the optimal type for processing money (in SQL Server 2008 it is “money”, but this all changes).

OTHER UPDATE: the cost of the inherited element should be zero, not zero (and in fact there is once an indirect cost in the form of taxes, but I'm distracted ...). Therefore, the value table should be in the table of goods, even if this value is zero. It should not be null.

Let me know if you have any questions.,.

Answer 4

Why do you need to normalize it?

I see some data integrity problems, but no obvious structural problems.

The implicit relationship between “purchases” and the presence or absence of value / currency is complex, but has nothing to do with keys, and it does not really matter much.

If we want to be purists (for example, this is intended for homework), then we are dealing with two types of items, inherited items and purchased items. Since they are not the same type, they should be modeled as two separate objects, that is, InheritedItem and BoughtItem, with just the right columns.

To get a combined view of all the elements (for example, to get the total weight), you should use a view or UNION sql query.

If we look for an object model in a database, we can decompose the common supertype (Item) and model subtypes (InheritedItem, BoughtItem) using foreign keys in the supertype table (the ypercube explanation below is very good), but it is a very complex and less reliable future than subtype modeling.

This last point is the subject of many arguments, but, in my experience, modeling specific supertypes in a database leads to pain later than leaving them abstract. Good thing waaay is probably beyond what you wanted :).