DB Table and SQL Update Command

Question

I'm creating database tables for a grocery store inventory. So far, I have a product table that include the SKU number, the company name, and the category name). And I also have a price table that include the SKU numbers and their price. Other tables that I'm considering of making are a table for each category (e.g. produce, can goods, dairy, etc). The primary key is the SKU number.

I need help verifying/clarifying the following:

Is it necessary to include price in the product table? I feel not, but I'm not sure.
If there's an update in price and I have price as an attribute in both tables, would I need to make an update in both tables? Yes(?)
It's better to have price as an attribute in the price table only instead of in both product and price tables, because I only to update one table.

Thank you for all your help!

Denormalization (data redundance, in your case) is admitted on relational model only as a workaround of performance issue: it's always bad introducing it from beginning. So, forget about duplicating it. In your case, a price table would be good if you need a price history or you have different prices for different targets; otherwise, just put the price directly on main table. — dbra
– dbra, Commented Nov 7, 2013 at 10:12
It looks like the Product data is fairly static but I imagine prices will change over time, discounts and promotions etc. So I would put price in a price table only. I would also consider having a Start and End date for each price record if you need to change the price but keep the price history for example. — Kevin Horgan
– Kevin Horgan, Commented Nov 7, 2013 at 10:17

OGHaza · Accepted Answer · 2013-11-07 10:16:50Z

1

You shouldn't include price in both tables for exactly that reason, you would need to update both - which means somewhere along the line you'll probably end up with conflicting prices in the 2 tables.

You could include the price as a field on the products table, but if you did so you shouldn't have a price table at all.

Keeping a price table is fine though, just use the SKU as a foreign key to refer to the corresponding product in the products table. I assume the SKU is a unique ID for a type of product?

Which way is better? If it is a direct 1-1 relationship where each product has exactly 1 price I would just store the price in the products table and get rid of the price table entirely - that way you don't have to JOIN to get the value each time.

answered Nov 7, 2013 at 10:16

OGHaza

4,7957 gold badges26 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

DB Table and SQL Update Command

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related