Q:  

This has to do with a data design issue. The setting is a shipping company, but I'll ignore the usual from/to, customer, consignee, etc. elements.

The issue has to do with the Shipment, Invoice, Line Item, Charges, Commodity and Container.

A single shipment can span multiple Invoices. Each Invoice can have multiple Line Items. Each Line Item is for one Commodity. Each Line Item can be associated with one or more Containers. Each Line Item can be associated with one or more Charges. When I lay this out, the relation between Line Item and Containers looks different than the relation between Line Item and Charges: The FACT table gets all screwy because there can be more Charges than there are Containers, or more Containers than there are Charges for any given Line Item.

I'm tempted to make two FACT tables, but am leery of any downside to such a design.

Any suggestions?

A:  

Adrienne Tannenbaum's Answer: You should start by doing an E-R (entity-relationship) model before getting into your facts and dimensions. This model will illustrate the business relationships between all of the entities above and give you sure-fire answers to how everything connects. The primary and foreign keys will become illustrated.

Chuck Kelley's Answer:I was once told not to design on the fly, but I just don't listen always. This may or may not be a good way to design this. I would have lots of questions to ask and have answered before providing a design. That being said...

I don't see two fact tables at all. Can you divide the charges/containers among themselves? For instance, you say there are multiple charges for a container - can you sum the charges or have different charge types applied to it? Then if you have multiple containers per charge, can you percentize the charge to each container and use that as a measure in the fact table? If so, then I believe this is a straightforward fact table. If not, then there is more here then meets the eye.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access