Relation design problem

[samyot2002]

Hi,

I am creating an inventory control database, and my products are using serial numbers, I am stock in desinging the tables needed .

Produts in stock 50

I need to store 50 serial numbers for that product.

One product number has many serial numbers.

Products can be in many locations (loc 1, loc 2 ...)

So I need to receive my stock enter the total quantity and its initial location and record all serial numbers.

Those products will be given to technician to put in there truck for install.

I need to transfert ex: 10 product xyz in his truck and transfert the location to his truck number and the serial numbers of those 10 products xyz.

I cant seem to visualize the tables and relations.

Can anybody help please.

Thanks
Sylvain

[cao]

Hi,

I am creating an inventory control database, and my products are using serial numbers, I am stock in desinging the tables needed .

Produts in stock 50

I need to store 50 serial numbers for that product.

One product number has many serial numbers.

Products can be in many locations (loc 1, loc 2 ...)

So I need to receive my stock enter the total quantity and its initial location and record all serial numbers.

Those products will be given to technician to put in there truck for install.

I need to transfert ex: 10 product xyz in his truck and transfert the location to his truck number and the serial numbers of those 10 products xyz.

I cant seem to visualize the tables and relations.

Can anybody help please.

Thanks
Sylvain

Very abridged design but should get you on the right path

1 Table Stock Master
Stock Code
Description
Selling
Cost
Qty On Hand

2 Table Stock Bin
Bin Location
Stock Code
Serial No
Qty

3 Table Stock Trn
Date
Transaction Code
Details
Stock Code
Serial No
Qty

Now the tricky part
Only Post to Stock Trn which can also be used as a log table
Create stored procedure / business rule to update table stock master and table Stock Bin

Transaction code Rcp = Receipts
- Increment Stock Master (Qty On Hand)
- Create new Stock Bin Reacord

Transaction Code Sle = Sale
- Decrement Stock Master (Qty On Hand)
- Decrement Bin Record (Qty)

Transaction Code Trf = Bin Transfer
- Increment Bin Record (Qty) receiving Bin
- Decrement Bin Record (Qty) outgoining bin

In the case of serial numbers the bin record could be deleted when qty=0 as it should never be used again.

Query on Stock Master will show available for selected stock code
Query on Stock Bin will show all stock codes with serial numbers
Query on Stock Trn will show all transactions for Stock Code and / or serial no selection

This layout will work for lot number stock items as well

Note that the db maintains the stock levels thereby ensuring integrity

Hope this helps
Regards Cao