Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

Consider the price of products in the table differs in each state. If the price varies in district I also have to mention it. How can I do this for more than 5 states?

product Name   Price  Wholeseller Price Retailer price   state     district
-------------|------|------------------|---------------|-----------|------
fan          |   6  |   12         |     18            | delhi      name1
wheels       |   10 |   17         |     27            | delhi      name1
biscuits     |   5  |   10         |     23            | delhi      name1
biscuits     |   6  |   11         |     24            | delhi      name2
fan          |   7  |   13         |     20            | karnataka  ...
wheels       |   9  |   16         |     26            | karnataka  ...
biscuits     |   5  |   12         |     25            | karnataka  ...

Image of table here.

Here I'm repeating the product for each state. Is it a correct method? Should I make a separate table for states & districts or is there a need to normalize?

share|improve this question
1  
Yes, this structure can (and probably should) be normalized. – Jon Seigel Feb 16 at 17:06
How can i normalize it – Kumar Htabmas Feb 16 at 17:08
please take a look at my normalized table(tinypic.com/view.php?pic=xc9bg9&s=6).. can you please give me an idea – Kumar Htabmas Feb 17 at 5:15

1 Answer

up vote 2 down vote accepted

You should normalize your tables. In third normal form (3NF) you tables would look like this:

enter image description here

Notice that this is different from your normalized table picture in two important respects: First, states and districts should be separated into two tables so you don't repeat the state names. Second, and more importantly, prices depend on district and product. Therefore the price should be in the intersection table between district and product, not in the same table as states and districts.

share|improve this answer
thanks for your answer @Joel Brown state (st_id,st_name) district (d_id,d_name,st_id) price (price_id,min_price,max_price,pid,d_id) product (pid,p_name) dateUP (date_id,date,price_id) Do i'm right now – Kumar Htabmas Feb 19 at 14:21
@KumarHtabmas - Yes, looks good. – Joel Brown Feb 19 at 14:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.