Making sense of these business rules

database-design

My scenario is a shop/warehouse and I have a Product table which contains basic product details including it's name, price, quantity on hand etc. I have a Customer table that has the shops customers and their details. And then I have a table that is confusing me: the Despatch table. The business rules for the Despatch table are as follows:

Its purpose is to assist the warehouse staff in quickly locating
products that are needed for the daily delivery run to customers. It
contains for each product the number of units needed for delivery. The
warehouse shelf location is also stored to facilitate quick retrieval
of the product. Thus the table stores product name, shelf location and
the number of units required for dispatch. Note that the product name
is stored instead of a product code as the name is much more
meaningful to the warehouse staff than a cryptic code. In order to
optimise performance the table is preloaded with the product name and
shelf location for each product.

Dispatch table

Disp_id     Number(4)
Prod_name   Varchar2(30)
ShelfLoc    Varchar2(6)
Quantity    Number(5)

Then finally I have a Sales table which holds all the sales transactions in the shop and is the bridge table between the Product, Customer and Dispatch table.

My confusion with the Dispatch table is that logically it seems like it would behave as though there's a one-to-many relationship between Product and Dispatch tables, but the business rules make it seem like it's a one-to-one as it asks me to preload all the products even if there are no deliveries/dispatches to be done? I guess I don't fully understand the concept of 'dispatch'.

Best Answer

Presumably the Disp_id is a non-unique field as you'll have an order table which contains the customer name / delivery address etc

so you'll have an order has 1-+ dispatches The product table will cant be a 1-1 relationship with dispatch otherwise only one person would ever be able to order that product regardless of how many they order so the product -> dispatch needs to be 1-+

looking at the information here this is less a table you want to create and more a View since all that information will be stored in other locations you dont want to duplicate things, you just want to derive the locations

A view based on the 

[Orders]-> OrderID (Is the dispatch ID)
[Orders]-> Product ID -join-> [Products] -> ProductName
[Orders]-> Product ID -join-> [Products] -> Shelf Location
[Orders]-> ProductQuantity

(I used to work in a warehouse and thats how it used to work there and makes sense, and fuflls all the needs that I can see you wanting)

There's no reason to load all of the products, you want to just load the products of an order

Think of the Dispatch as part of the Full order, the warehouse staff only needs to know that this gets picked up from there and gets placed with other items of this dispatch ready to actually be dispatched

Hope that helps (it is based on my understanding of what I've read) Ste

Related Question