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.

StackExchange Database Administrators Q#42863, answer score: 7