Unit conversion table design in database
Posted on 2009-07-02
I am new dabase guy and recently I got a small project in charge of a databse designing. The table in the excel would be
Item # Unit Price
11111 EA 5
11111 DZ 60
Actually 11111 is the same item but with different unit. In my database, I could put two surrogate key to treat them as different items. However, if I update one item price , it would automatically change the one with different unit.
1. Is it necessary to create two tables to distinguish this ? and how could I do that?
2.I know query could handle if we know the relation between EA and DZ, but if the relation between EA and BX is not stable (eg, one box might have 6 or 5 or 4 items ), the query would be really hard to be generalized for thousands items.
Thanks all of you guys.