How can a database be normalized when an optional field causes denormalization?

Question

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?

Answer 1

You can try creating something like this (please excuse the crudity of the image):

Answer 2

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.

Answer 3

You can get rid of the anomaly by changing the FOREIGN KEY constraint (from Widgets to Carts) to include the StorageLocationID:

CREATE TABLE Widgets
  ( widgetID              NOT NULL
  , storageLocationID     NOT NULL
  , cartID                NULL
  , PRIMARY KEY (widgetID)
  , FOREIGN KEY (storageLocationID)
      REFERENCES StorageLocations (storageLocationID)
  , FOREIGN KEY (storageLocationID, cartID)
      REFERENCES Carts (storageLocationID, cartID)
  ) ;

This usually requires a UNIQUE constraint (or index) on Carts:

CREATE TABLE Carts 
  ( cartID                NOT NULL
  , storageLocationID     NOT NULL
  , PRIMARY KEY (cartID)
  , FOREIGN KEY (storageLocationID)
      REFERENCES StorageLocations (storageLocationID)
  , UNIQUE (storageLocationID, cartID)
  ) ;

Answer 4

Your diagram has a table for Widgets with a StorageLocationID and a CartID. Not sure that makes sense logically, since the StorageLocationID refers to something physical and fixed, and the CartID can move over time.

You don't seem to be tracking things over time though, so perhaps that is the disconnect.

But when I think of this scenario I think that a widget can be stored in a location, and it can be placed on a cart, and that cart can then be moved to various locations over time.

This also assumes that a widget exists (has been produce already), is currently stored in a location, may (or may not) be on a cart, and that cart can be anywhere.

HTH