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:

1. Is it necessary to include price in the product table? I feel not, but I'm not sure.
2. 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(?)
3. 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!

Answer 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.