We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Unit conversion table design in database

oiyzhx
oiyzhx asked
on
Medium Priority
741 Views
Last Modified: 2012-05-07
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.
Comment
Watch Question

what I can suggest is to create one table for unit and it's every possible conversion unit with conversion factor, which will help you for sure. for eg:


Unit        ToUnit        conversion
EA          DZ              12
DZ         EA                5
President and Btrieve Guru
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks i got it ~~~
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.