3

I'm developing a inventory system with optional serial no tracking (which users can turn on for items they select).

in order to keep stock records currently i came up with two options.

Option 01 use single stock table with following schema

dbo.stock{
stock_id,
item_id,
item_qty,      //for serial items this will always be 1
serial_no,    
grn_id         //to keep track of GRN
}

e.g. if i have 5 TVs to track using serial no, i'll have 5 rows with serials and quantity as 1. if i have 10 DVDs with no inventory tracking i'll have 1 row with qty =10

Option 02 use two tables as follows
table 01: keep track of inventory batches

dbo.stock_main{
stock_id,
item_id,
item_qty,      //for serial items this will always be 1  
grn_id         //to keep track of GRN
}

table 02: keep the list of serials in this table for particular inventory batch

dbo.stock_serials{
stock_serial_id,
stock_id,
serial_no,
used           //1-used, 0-not used
}

each time serialized item is issued i'll check this table and prompt users to select a not used serial no.

all searches for serialized inventory database/design/model goes to various vendors of barcode systems so i couldnt find much info on web too.

is this the correct way of doing this? or am i missing something or there are smarter ways to address this? thanks.


EDIT
To add more details about my product setup, Product: Id: 1, name: Samsung TV, barcode:6473647
All Samsung TVs have same barcode, but each TV has its unique serial no.

  • The systems I have used have a separate table for tracking serial numbers. Actually come to think of it, I have used one with the serial numbers in the product table. It could be done either way depending on your products. What if the product is a kit/combination and has multiple serials attached? Just something to consider. – Jacob H Jul 6 '17 at 14:15
  • Actually my product table only contains basic meta deta of products WITHOUT quantities. Lot no, serial, expiry and other details vary from grn to grn and hence need to keep with separate stock recording method. Yes bundled items with multiple serials should be another issue to consider too. – eric Jul 7 '17 at 4:54
  • Edited the question to provide sample dataset I am working with – eric Jul 7 '17 at 5:00
  • 1
    @CoderAbsolute yes, later we got the requirement for grouping and we had a flag 'isComposite' in main table to identity whether it is a normal item or grouped item and then we used separate member table to map member items (normal/serial) for that particular composite item. – eric Mar 17 at 15:39
  • 1
    @CoderAbsolute hi, srry for the much delayed response. yes we use member table for that too – eric May 23 at 7:15

Your Answer

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

Browse other questions tagged or ask your own question.