Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> 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
![]() |
![]() |