Help on table design

[SF]

Hi,

I have developed a database for a small electronic shop. It works quite well
except that I need to address to some areas such as managing items with
serial number and maintaining warranty for some serialized products. I have
a barcode scanner that can facilitate in reading the serial number of the
products but I cannot determine the table structure for it.

I would appreciate advice from the NG. Below is my table design



tblProducts
ProductID
ProductName
.....

tblOrder
OrderID
OrderType ' 10 for receiving; 20 for Sale; 30 for returned...
OrderDate
....

tblOrderDetail
OrderID
ProductID
Qty
UPrice
....

 

[rpw]

Hi SF,

You say you "need to address to some areas such as managing items with
serial number and maintaining warranty for some serialized products." For
this I'll offer suggestions for setting up your tables.

Serial Numbers: One Product can have many individual units, therefore there
should be a table for ProductUnits that would include the individual serial
numbers (along with the foreign key of the Product).

Warranties: The warranty (actual document, details of what's covered, how
long things are covered - duration, etc.) should be associated with the
Product. The beginning date of the warranty should be associated with the
individual ProductUnit. Expiration date can be calculated from beginning
date plus duration (on a form or report, not a table - just in case I'm not
clear).

"Managing items": I'll apologize in advance if I'm misinterpreting.... You
don't list an InventoryTransaction table. This might help you to know which
serial numbers have been recieved, sold, returned, etc. and also current
stock condition of any given product.

Does this help?

rpw

 

[SF]

RPW,

Thank you very much for your advice.

SF

Hi SF,

You say you "need to address to some areas such as managing items with
serial number and maintaining warranty for some serialized products." For
this I'll offer suggestions for setting up your tables.

Serial Numbers: One Product can have many individual units, therefore there
should be a table for ProductUnits that would include the individual serial
numbers (along with the foreign key of the Product).

Warranties: The warranty (actual document, details of what's covered, how
long things are covered - duration, etc.) should be associated with the
Product. The beginning date of the warranty should be associated with the
individual ProductUnit. Expiration date can be calculated from beginning
date plus duration (on a form or report, not a table - just in case I'm not
clear).

"Managing items": I'll apologize in advance if I'm misinterpreting.... You
don't list an InventoryTransaction table. This might help you to know which
serial numbers have been recieved, sold, returned, etc. and also current
stock condition of any given product.

Does this help?

rpw