Suppose you have a warehouse full of widgets. Each widget in the warehouse is stored in a specific identifiable location within the warehouse. You might have a schema which looks like this:
A widget may also be (optionally) located on a cart in the warehouse, which could lead to a schema like this:
However, a problem arises because a cart can also only be located in one location within the warehouse, which turns the prior schema into something like this:
Howver, this schema is denormalized and can result in anomalies, such as if the data was:
widgetID storageLocationID cartID ======== ================= ====== 1 foo A 2 bar A
Although both widgets are located on the same cart, the database shows them in different locations which is not possible.
If a cart was required, it would be relatively simple to address this normalization issue by simply removing the storageLocationID from the Widgets table. However, because a cart is not required, that solution will not work.
How can this data be structured to eliminate the anomalies?
Best Answer
You can try creating something like this (please excuse the crudity of the image):