DB Design

[Kumar Arun]

I am designing a database for inventory management for a wholesale dealer. In my case, the firm will receive stock allocation but the actual physical stock will only be received later. So some times the actual physical stock received may be less than the allocation or vice versa. excess or shortage of actual physical stock will be adjusted with the next month's allocation.
To make it complicated there might be price difference also.

At any give point of time, the opening, closing stock should be known.

Can you provide any suggestion / advices as to design a normalized DB structure for this scenario ?

[tesuji]

Hi Kumar,

this is a very nice task, but I would suggest you take the first turn starting to design that data model by yourself. Then post your (maybe imperfect) results, and we will help you willingly.

krs,
tesu

[Kumar Arun]

Hi Kumar,this is a very nice task, but I would suggest you take the first turn starting to design that data model by yourself.....krs,tesu

Yes finally i created a mdb file. It might not be the perfect one. and i hope you will give me guidelines regarding the same. So should i upload the mdb file itself or the table structure only. I am in this forum quiet recently, so pls excuse my ignorance.

[tesuji]

You can upload the SQL script for creating the db, also the mdb file (I have access to acess 2003 only). Better: Upload the ERM, if you have sketched one.

[Kumar Arun]

You can upload the SQL script for creating the db, also the mdb file (I have access to acess 2003 only). Better: Upload the ERM, if you have sketched one.

Thank you for the quick response. I have not made the ERM. So i will upload the mdb (Access 2003). I am uploading the mdb as a zip file. I think some structural changes would be needed to implement the requirements. Please Advise.

[Kumar Arun]

Thank you for the quick response. I have not made the ERM. So i will upload the mdb (Access 2003). I am uploading the mdb as a zip file. I think some structural changes would be needed to implement the requirements. Please Advise.

Hello, Have you gone through the table structure. Could you please give me some advise.

[tesuji]

I have already installed your database, also made some changes.

Pls answer following questions:

1. What is stock allocation?
1.1 an amount of a certain product allocated (= reserved) for delivering
to customers in the near future?
1.2 The amount of products ordered from company's supplier not yet delivered, ordered by your company's purchasing department.
(if this is true, what is the procedure to move ordered products into "on stock" when they were delivered? So you need a stock-receipt function. Kind of account transfer.

2. What about the amount of products already sold to customers, should this also be recorded? There you would need a shipping-goods function.

3. What is employee, the person of your company who has carried out the "allocation", he who ordered the amount of products from your supplier?

A Wholesaler usually must keep the records of all product movements exactly containing amounts, costs, dates, receiver ..., therefore, for a real application all those movements need to be handled within you design.

krs,
tesu

[Kumar Arun]

I have already installed your database, also made some changes.
Pls answer following questions:............................
krs,
tesu

First of all, sorry for the delay. I was not at my desk for the last days.

Now i will clarify the details.

Stock Allocation means an amount of a certain product allocated for delivering to customers in the near future. The actual stock will be received at a later stage only. Only after the stock is received, it will be distributed to the customers.

to make it clearer. (and this is not a typical sales inventory)

A centralized agency is supplying materials to customers. The agency will not be supplying directly. Instead they will deliver the goods to a wholesale dealer on each area. The dealer will in turn be responsible to supply the material to the customers in their area.

Before the agency is delivering the goods, they will send an allocation invoice. This will contains the type and quantity of materials, and also the customers to whom it is to be suppled.

Employee does not have much relevance. It is for recording purpose only. just to know who handled what...

"A Wholesaler usually must keep the records of all product movements exactly containing amounts, costs, dates, receiver ..., therefore, for a real application all those movements need to be handled within you design." - Yes i agree with you ....

Now i have to design a DB for the wholesaler. At least that is what i am attempting. Your suggestions an advice would be greatly helpful.

[tesuji]

Hi arun,
thx for giving further details. Here is the result (sure, incomplete!) attached.
Drawing of the ERM has been done by tool from Charonware, which has better feature to create entities and relationships. PK = primary key, FK = foreign key (not identifying), PFK = foreign key what is also part of primary key (identifying). The ERM does not contain formal errors what has been checked by the Charonware tool. But it might have still some logical errors.

Remarks
Transaction: There could be more than one transaction for a specific allocationID because of partial deliveries, therefore transactionItem.
State: the state of an allocation, for example: not delivered, partly delivered, etc.
Stock: what physically is on stock, delivered by supplier. This is decoupled from allocation and transaction, what may also mean that delivered quantities could be split into parts for satisfying different allocations.
Unit: not that important (because I took some of the above design from an older design once I did, it appears here too)
Maybe there must also be a relationship between customer and wholesaler, if a specific customer buys from various wholesaler?
Invoice (not incoice): If there are partial billings for a specific allocation, there must be invoiceItem added, similar to transaction.

krs,
tesu

ps. I can also generate sql script for Access 2000 and MySQL from above ERM.