Same product name with different price in different states

database-designnormalization

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?

Best Answer

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.

Related Solutions

Name of the product price, line-item database pattern

Not every approach to common problems has a name. Certainly at least not a commonly agreed name. The term "design patterns" applies just as well to common database designs as it does to OOP. I would say data modellers are a little less enamoured with the idea of design patterns than OOP programmers.

I'm not familiar with a commonly agreed name for the design approach that you're describing. I call it transactional logging.

As an aside, the price in the sale item table is not technically redundant, even if the price hasn't changed. The price in the product table is the current book price, the price in the sale item table is the actual price charged. There is no way of knowing for sure what price was actually charged from looking at book price. Even if you have a book price history table it might have been possible, depending on your system's rules, for the price to be overridden manually. The two data attributes are semantically different, even if they happen to be coincidentally equal for some period of time.

Database Design – Optimum Structure for Product Data Database

All you need is two tables. 1 for Products and 1 for Attributes. You would then have a one to many relationship from the Products table to the Attributes table.

This means that each product can have as many attributes as it needs.

Table structure would look something like this.

Products
Product_Id INT PK Auto Increment
ProductCode VARCHAR(255)

Attriutes
Attribute_Id INT PK Auto Increment
Product_Id INT FK
AttributeName VARCHAR(255)

AttributeValues
AttributeValue_Id INT PK
Attribute_Id INT FK
AttributeValue VARCHAR(255)

Best Answer

Related Solutions

Name of the product price, line-item database pattern

Database Design – Optimum Structure for Product Data Database

Related Question