How can a database be normalized when an optional field causes denormalization? 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?
You could consider a cart to be a storage location.

Simply add attributes to the table so that a location can be identified as type "cart" or "shelf", like so:



If there's a requirement for a hierarchy - for example, if a cart might be stored in a storage location itself - you could define the hierarchy within your storagelocation table.



This is not a perfect solution - it may make it difficult to enforce restrictions that a cart must exist in a storage location (like a bay or aisle).

However, in most database engines it's straightforward to enforce some business logic here using CHECK constraints.