minimize number of tables in MySQL?

Question

Basic question about a good way of organizing data in a database. Let's say I have a search site for multiple stores. Each store has a number of products. What is the best way of organizing price and inventory data? I see two ways of doing this:

1. a table for every store , with the first column or two identifying a product, a column for inventory, and a column for price
2. a table for inventory, and a table for prices, where the first column in both identifies the product, and separate columns for every store

The first option seems more convenient, but results in a lot more tables. Is it better to minimize the number of tables or does it not really matter, in terms of performance?

Also, for the first option, should I keep this data in a separate DB or can I store general product and store detail tables in the same DB?

Answer 1

Neither. You should normalize the table. You can use something like this:

Product Store Price
FooBar  CA    1.22
FooBar  MA    1.34
BarBaz  CA    2.30
BarBaz  MA    1.99

The product and store can be foreign keys to another table, and you might also wish to consider using surrogate keys instead of natural keys. One advantage of this is that if for example a product name changes, you don't have to modify any keys.

Answer 2

Your two options each violate principles of good relational database design.

1. Having a number of tables with the same design is a sign you are doing something wrong.

2. Having a number of columns all storing the same data (prices) is a sign you are doing something wrong.

3. Having the number of tables or columns change when your business expands (adding more stores, for example) is a sign you are doing something wrong. They can change when your business changes (needing to start charging tax, or tracking customers), but not when it merely expands.

You don't mention whether prices change per store, I'm assuming inventory does. If prices change per store, then you can create a table like:

Product  Store   Price   Inventory
Shoes    Boston  54.00   27
Juice    Miami   1.49    100

If prices are fixed per product, then you can drop the Price column from that table and make a new one for product prices:

Product   Price
Shoes     54.00
Juice     1.49