There are many retailers who use private bar codes to describe each item they deal with instead of using Universal bar codes (UPC, EAN i.e) or use both methods.

I want to develop a system taking that into account, but I want to discuss some tables with you.

1- Items (which is the lookup table for all items the system uses).

2- Purchase_invoice_detail (which will hold the details of the invoice(Items being purchased).

3- Sales_invoice_detail (Same as the above, but for selling items).


1- Items(id, name, min_qty_for_demand, default_sale_unit, default_purchase_unit, UPC_EAN, ....).-- The bold column holds the universal bar code of the item.
2- Purchase_invoice_detail:

* Each item has a number of attributes like (Expiry_date, color, size,....), and the private bar codes retailers use should describe those attributes( for example, the bar code "1152019" describes some item whose id is "11", and it's size is large which is "5", and it's expiry_date is "2019",

same item with another size and another expiry date would be "1162020".

- the question now is about the "Purchase_invoice_table" table, should it hold the attribute columns(expiry_date, color, size) besides the "item_id" in the "Items" table, or can I just store the bar code value which basically describe those attributes besides the "item_id" column as well?

3- sales_invoice_detail:

Same question as above.