Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

4 Answers 4

up vote 3 down vote accepted

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

share|improve this answer

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.

share|improve this answer
    
+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? –  Shawn McKnight May 23 '13 at 16:17

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)
  ) ;
share|improve this answer
    
+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. –  Shawn McKnight May 23 '13 at 16:02
    
What DBMS are you using and what constraints is it not supporting? Unique or compound foreign keys? –  ypercube May 23 '13 at 16:05
    
The DBMS is Unidata. I can enforce Unique constraints but not compound foreign key constraints. –  Shawn McKnight May 23 '13 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. –  ypercube May 23 '13 at 17:29
1  
Why do you think this isn't in 3NF? –  ypercube May 23 '13 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.

HTH

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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