1

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

1

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

1

The Products table should contain static data about the products (name, sku, weight, etc). Dynamic data such as quantity on hand and location (bin number, shelf number, whatever) should be contained in a separate Inventory table. In fact, if location is stable enough, it could instead be in the Products table.

Whoever wrote the description of the dispatch table should be moved to a position where they have no access to sharp objects and can no longer cause mischief. In the first place, the description should be for the "data source" rather than "table" because that source could very well be (and in most cases should be) a view rather than a table. Second, the description should never specify the physical layout of the underlying source. If the application needs to see data in a particular form (the product name rather than number), then specify that form. But that does not mean the data must be stored in that form.

How the data is actually stored should never be part of a business rule or general specification. Ask for the data wanted, in the format wanted, and let the people who know what they are doing provide it in the best way possible.

In fact, the dispatch table looks every bit like a join of the Products, Inventory and Sales tables showing all the products needed to ship today's orders -- except for disp_id which seems to serve no function whatever. This is the job of a view.

0

You might be able to find somebody else's design for an order fulfillment system in one of the models presented in database answers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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