I am building one online bakery store. Here I will have to manage products like cakes and pastries. A half kg cake could have price of 250 where as 1 kg cake will have price of 450. That is, it doesn't double the price. So how should I manage this situation which holds for various products across the tables. Should I keep any separate table to maintain slabs (for 1 kg 50% increment)? Any suggestions are most welcomed, as currently I am working over the architecture of the database for this store.


If there are arbitrary prices for each pastry and weight thereof, then they would need to be stored individually. That is unless there is a common multiplier that can be applied to the weight of the pastry to get its price.

If a pastry will always be the same price and only sold in specific weight values - then this is a one-to-many relationship (one pastry can have many prices - determined by different weights) then you would want a pastry table, and a pricing table.The pricing table would contain the price, the weight, and a foreign key to the pastry table.

However, if there is a common multiplier that can be applied to the weight to determine price - then you would only need one table - for pastries. The multiplier is functionally dependent on the pastry, and therefore would be stored in the pastry table. This assumes you aren't going to keep track of historical multipliers - or that the multiplier won't change.

Without more detail I had to make some assumptions in my answer. You should derive the functional dependencies for your schema, then follow the normalization rules.

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