What is more efficient? An additional "text" column or multiple tables?

[ComputerPsi]

I've got a lot of historical price data that needs to be stored. Each product has a historical price. Each product has about 500 or so listings for historical prices. There are about 7000 products.

So I am thinking I can either make 7,000 tables each with about 500 rows (Each table's name contains the product name)..

..or 1 table with 3,500,000 rows (Add an extra column with the product name).

Which do you think is better and why?

Note that there will be a few other tables too. Differentiating between table names will not be an issue.

[gassaz]

Why a table for product???

 

Which option will take less time??

 

I prefer to make one table... why?? well.. first time!!! and second database normalization.

[slushpuppie]

i'd make a table like:

 

`id`, `product_name` - which would have one entry for each product

 

and then another table with:

 

`product_id`, `historical_price` - which would point to the `id` from the products table

 

that seems to make the most sense to me.

[ComputerPsi]

Sorry apparently I did not describe the problem well enough:

Each product has about 500 different historical prices. This consists of two columns - date and price.

So on different dates you have different prices.

However I have 7000 different products. I can either make a third column for the product name or have a separate table for each product.

There is no difference for me in creating 7000 tables or creating a new row for each new product.

I am asking which is more efficient. Will SQL perform better with one table using 3,500,000 rows or with 7000 tables with 500 rows?

[gassaz]

There is no difference for me in creating 7000 tables or creating a new row for each new product.

I am asking which is more efficient. Will SQL perform better with one table using 3,500,000 rows or with 7000 tables with 500 rows?

 

Well, it depends.. depends of what??, a little example:

 

Somebody wants a report for a list of products that changed his value on 2009-02-01.

 

If you use 7000 tables, how you will do that query??

[fenway]

Yeah ... 7k tables ... bad idea.

[ComputerPsi]

Somebody wants a report for a list of products that changed his value on 2009-02-01.

 

If you use 7000 tables, how you will do that query??

 

Simple:

Lets say the name of a product is "apple". The corresponding table name is "P_apple" ...so to change the product's historical price you say something like:

UPDATE `P_apple` SET price="1.99" WHERE date="2009-02-01"

 

Yeah ... 7k tables ... bad idea.

Is 3,500,000 rows a better idea? Why?

[gassaz]

Not, i mean ...

 

Make a query for all the products that changed price value on 2009-02-01.

 

For me too it's better a large table, the performance of the query will depends of the DB structure (indexes)

 

if you like make a lot of tables do it.. your querys will fly.. but the time to make the querys it will be a long time..

 

Good look..

[fenway]

3.5M rows is nothing.