Re: Too many columns (rows) in a table

A copy of this was sent to caramel98_at_my-deja.com (if that email address didn't require changing) On Sun, 23 Jan 2000 09:03:48 GMT, you wrote:

>Hi,
>
>I have to create a table for the following:
>- 100 000 products
>- each product has 10 descriptions
>- each description must be in 30 languages
>
>If I create one table to contain all of the properties of the products,
>I'll have at least PRODUCT_ID + 30*10 = 301 columns.
>
>An other choice can be to use two tables:
>- table PRODUCTS with PRODUCT_ID and other columns
>- table DESCRIPTIONS with the columns
> - PRODUCT_ID
> - DESCRIPTION_ID
> - DESCRIPTION_LANGUAGE
> - DESCRIPTION
>
>The table DESCRIPTIONS will contain 100 000*10*30 = 30 000 000 rows
>

another choice:

products ( product_id PRIMARY KEY, and other columns )

descriptions ( product_id REFERENCES products, description_language, description_1, description_2, ...., description_10, primary key( product_id, description_language ) )

Since you'll always access descriptions via the primary key index on product_id, description_language (eg: your query is always:

select * from products, description
where products.product_id = description.product_id and description.language_id = :the_language_of_choice;

) it won't matter much if there are 3million, 30million or more records in the second table.

I like this approach better as it allows you to easily add and drop languages -- no structural changes needed.

Your first 2 table design is nice if you need to add/remove some description fields over time but is a little harder to query.

>Is there a best way ?
>
>Thanks for any help.
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Jan 23 2000 - 13:12:41 CST