I'm having trouble designing tables to store quotes. In this system, product configurations (custom built products) are quoted at a variety of "locations". The price then is a function of the product configuration options and the dimensions of the location. I need to conserve space/bandwidth so that this will work well on a handheld, although hopefully I do not have to compromise the design just for this.
So if there is a particular product configuration to be quoted against 30 locations, it would seem pointless to store this product and, say, 20 options, for each location. So it would make since to have this configuration (product header and option details) stored once and match them to the locations they are being quoted at.
The problem comes when the user needs to adjust the options at just 1 location. Is this a new product configuration? If so, should I have just created all 30 locations x 20 options up front, even though in most cases the options will just repeat? If I just created the 1 configuration and an option is changed somewhere, do I create a second configuration? But then if the option is changed back should I collapse the 2 configurations back to 1 since they are effectively identical? Seems like too much work, but also it seems like creating these 600 rows up front is also a waste.
Any ideas on how to structure the tables properly?
Do the 600 rows - it is much easier, as you have pointed out yourself. And don't worry so much about numbers of rows. If you were using a spreadsheet to record this information with 30 rows for the locations and 20 columns for the options, would you worry about performance and bandwidth then? You would still have 600 separate pieces of information recorded (the cells) - and the spreadsheet probably contains more bytes of data including overheads than the database equivelant (bold assertion: I have no evidence to back that up!)