Hi,
I am designing table structure for any inventory system. The system support multiple item types, matrix, serialno, normal, ...

Also, it support multi barcode, which meant

1. Normal item could has more than one barcode beside of the default. However, while looking for stock status, only the default barcode will be listed.

2. Serial No. items only use one barcode, but with alot of serial no.

3. Matrix item has one default barcode, and each colour and size will have it's own barcode. Each of them will keep track of own stock qty.

At 1st, my table design for it is as:

Table: Multi BarcCode
InoID (FK), BarCode, MBarCodeID (PK), ColourID, SizeID, SerialNo

Table: Stock_Status
OutletID, MBarCodeID, Qty

From the design above, you may notice that, different coloumn will be filled based on different type of item type. Also, Some of the MBarCodeID may/may not stored in stock_status table based on item type either. I found that it quite complicated.

Then, I am thinking to one table take care of each item type barcode record, but.. may slow down the speed of search, reporting, maintenance..

So, I would like get some comments from you all..
Any ideas?

Thank you in advance