Quantity Per Unit value is not unique.

[mezo]

Hi all.
I'm experiencing an issue in my database design..
In my Products table I must assign a Quantity Per Unit column but the problem is the value of that column for the same product could be 5 or 6 or 7 .. how can I solve this problem in my database design..thanks

[tesuji]

hi mezo,

It depends on your datamodel where to store changing data. As for example merchandise management systems (also retail systems, ERP) usually distinguish between master data which remain almost unchangeable and transaction data which differs, for example such things like customers order data. If your customers can order a product in different quantities, say 5, 6, or 7 pieces / unit this information should be stored in an extra table, e.g. customers order item table together with item number, total price etc.

On the other hand you usually need a further table (and much more in real world applications) where you keep book which qty/unit can be assigned to a certain product, maybe also data to get total price.

So your core tables could look like these three:
masterdata(ItemNb#, Itemname),
Customerorderitems(orderNb#, ItemNb#, quantity, qty_per_unit, total_price),
productunits(ItemNb#, qty_per_unit#, price). (where # denotes primary key columns).

Maybe I can give you more help, if you show more details of your data model.

btw, it isn't a good idea to add three columns to your product table for recording the given three qty / unit because there would arise some serious problems when trying to create useful SQL queries or also when doing normalization.

krs,
cliff

[mezo]

Thanks Cliff for your input..
For data modeling I'm just in the beginning :) but what i am trying to design is an Inventory System which will track the changes made in the products store so data model will be like the other systems except for my Product table issue..

btw, it isn't a good idea to add three columns to your product table for recording the given three qty / unit because there would arise some serious problems when trying to create useful SQL queries or also when doing normalization.

Actually it could be three columns or 8 columns .. but it seems that this suggestion gonna work for me... so i'll apply it in my design and if there is better suggestions please let me know :) thanks alot for your help cliff.

[tesuji]

hi mezo,

again, you should not solve your problem by adding 3 or even 8 columns to you product table for storing qty/units, if you also plan to run some queries again that table.

Scenario: you may have stored some 1000 products which have different numbers of qty/units. Now your boss ask you to show him a sql select list where for each product those qty/units should be listed together with product name. How to do that?

1. using my small core data model, where qty/unit data is stored in an extra table:
select m.itemNb, m.name, p.qty_per_unit from productunits p, masterdata m
where p.itemNb = m.itemNb

2. all qt/units stored together with master data in your product table:
no way to construct the SQL select because for each stored product you need to know
how many qty/unit columns exist.

krs,
cliff

[trudge]

This is good advice. Whenever you have a field / column with multiple values that's a clue to pull it out and put it in a separate table.