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.
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'.