StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>There are three cases here (from the <code>Product</code> table's point of view):</p> <ol> <li><b>Many-to-one relationship</b>, e.g. many products having the same unit type - one unit type per product.<br>In this case the foreign key must be in the <code>Product</code> table referencing the primary key <code>UnitType.UnitTypeID</code>.</li> <li><b>One-to-many relationship</b>, e.g. one product can have multiple images - one image can belong to only one product<br>In this case the foreign key must be in the <code>ProductImages</code> table, referencing <code>Product.ProductID</code>.</li> <li><b>Many-to-many relationship</b>, e.g. any product can have many categories - any category might describe many products<br>In this case you will need a connection table that contain <code>ProductID</code>/<code>CategoryID</code> pairs, with columns being foreign keys referencing <code>Product.ProductID</code> and <code>Category.CategoryID</code> respectively.</li> </ol> <p>So, the design of <code>UnitType</code> (case 1.) and <code>ProductImage</code> (case 2.) tables is OK, but <code>FeeType</code> should probably be case 1. and <code>Category</code> should be case 3.</p> <p>BTW, it would be perfectly OK to have <code>NULL</code> in a foreign key column; it would not break the rules of normalization. So, for example, if some products do not have fees associated, you can have <code>NULL</code> in the <code>Product.FeeTypeID</code> column. But you will need to use an outer join in your queries to ensure that no products with no fees will not be excluded from the results.</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