data structure for tracking multiple warehouses

[douglasgifford]

I've taken on a legacy FM database for a wine importer; rationalizing the stucture, redesigning the input layouts, and adding features.

The importer sets up an purchase order (multiple cases of a variety of wines) to a government warehouse, then orders and ships from the government warehouse either directly to the customer or to one of two private warehouses. Goods are subsequently shipped from the private warehouses to a customer.

The current model doesn't take into account the private warehouses. There is a table tracking arrivals at the government warehouse and another table tracking shipments from the government warehouse directly to customers. I need to add the ability to track product at the case level to and from the private warehouses.

My instincts suggest that I need an intermediate table that tracks individual cases of wine (part-cases exist, but my client cannot sell by the bottle) to and from (where applicable) the government warehouse/the private warehouses/the customers. My common sense and my neighbour Dale suggest that that's going to mean a lot of records; on the other hand, its simplicity and correspondence to reality are appealing.

I'm thinking of a table representing each case as a record which would be generated from the Purchase Order table. Fields would be: a prime key; foreign keys to appropriate tables (most importantly the PO table); a location field representing either a warehouse or a customer (at which point we're finished with it); and either a 1 or a fraction to represent full or partial cases. Anyway, minimal data per record.

Thoughts?