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:

Original layout

A widget may also be (optionally) located on a cart in the warehouse, which could lead to a schema like this:

Carts added to schema

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:

Denormalized schema

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?

  • Why do you not set storageLocationID of Widgets to NULL if it's in a cart? Jan 28, 2017 at 7:50
  • @JMDCoalesce - It would still be prone to anomalies by doing that, similar to the "foo/bar" example above. If storageLocationID contains a non-null value while cartID is also a non-null value then you likely have an anomaly. Jan 29, 2017 at 15:28
  • It's less prone and you can add a constraint to make sure one of the two columns must be set. Jan 29, 2017 at 15:44

4 Answers 4


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

A locations table related to both storagelocations and carts, where cartid is nullable


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:

Possible normalised solution 1

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.

Possible normalised solution 2

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.

  • +1 Thanks for the edit to the original post. The second solution looks to be 3NF and would allow a cart to exist within a storage location. It does make querying this data rather awkward, however, because the storagelocation_id FK in widget could be either a physical location or a cart. The type would need to be evaluated to figure out which it was and then a self-join added if it was a cart to get the location. Any thoughts on how to do this while keeping the query simpler? May 23, 2013 at 16:17

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

  ( 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:

  ( cartID                NOT NULL
  , storageLocationID     NOT NULL
  , PRIMARY KEY (cartID)
  , FOREIGN KEY (storageLocationID)
      REFERENCES StorageLocations (storageLocationID)
  , UNIQUE (storageLocationID, cartID)
  ) ;
  • +1 because this method would eliminate the possibility of anomalies by forcing the DBMS to maintain the consistency of the data. I believe this still breaks 3rd normal form because the value of storageLocationID is dependent on the value of cartID. Do you know of a way to put the data in 3NF? I would use this method but the DBMS I am using will not support these constraints. May 23, 2013 at 16:02
  • What DBMS are you using and what constraints is it not supporting? Unique or compound foreign keys? May 23, 2013 at 16:05
  • The DBMS is Unidata. I can enforce Unique constraints but not compound foreign key constraints. May 23, 2013 at 16:22
  • I don't know then if it's possible. I don't have access to Unidata SQL specifications and from a brief look at the online specs, I can't even find if FOREIGN KEY constraints are supported. May 23, 2013 at 17:29
  • 1
    Why do you think this isn't in 3NF? May 23, 2013 at 18:56

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.



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.