Posted by Roy Harvey on 01/02/07 17:36

Use a single table, not many tables. Use a multi-column key that
includes the stock. When a new stock comes along it should be a user
transaction to add a row to the Stocks master table, not a database
administrator's job to add tables. The database design should be
stable, not in a constant state of change.

Performance of INSERTS does not change much between inserting to a
table with 10000 rows or 10000000 rows.

Roy Harvey
Beacon Falls, CT

On 2 Jan 2007 08:57:21 -0800, "Emin" <emin.shopper@gmail.com> wrote:

>Dear experts,
>
>If I have a number of related groups of data (e.g., stock prices for
>different stocks) is it better to put them into many tables (e.g., one
>table per stock) or into one big table (e.g., a single table where one
>column is the stock id other columns are the price and other data)?
>
>The single table approach with a clustered primary key including the
>stock id and other information that is different for each stock seems
>cleaner to me. But I worry that having a single table could hurt
>preformance when the database gets very large. The clustered primary
>key should make queries on the single table fast, but I'm worried about
>inserts. According to my vague understanding of a clustered primary
>key, records are physically stored on disk based on the primary key. So
>if I insert a record for a given stock does the database have to
>physicall "move down" all the records that are below that or does it do
>something smarter?
>
>To summarize:
>
> 1. Should I use a single table or many tables?
>
> 2. If using a single table, how are inserts handled by the DB to
>prevent inserts from getting slower and slower as more items are added
>to the DB?
>
>Thanks in advance for your help and advice.

[Back to original message]