StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Not only <code>Amount</code>, everything is dependent on <code>ItemID</code>, as this seems to be a candidate key.</p> <p>The dependence you have is that <code>Currency</code> and <code>Amount</code> are <code>NULL</code> (I guess this means Unknown/Invalid) when the <code>Procurement</code> is <code>'inherited'</code> (or 0 cost as pointed by @XIVsolutions and as you mention <em>"inherited items have no monetary cost"</em>) </p> <p>In other words, iems are divided into two types (of procurements) and items of one of the two types do not have all attributes.</p> <p>This can be solved with a supertype/subtype split. You have a supertype table (<code>Item</code>) and two subtype tables (<code>ItemBought</code> and <code>ItemInherited</code>), where each one of them has a <code>1::0..1</code> 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:</p> <pre><code>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 </code></pre> <h2>If there is no attribute that only inherited items have, you even skip the <code>ItemInherited</code> table altogether.</h2> <p>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". </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload