Re: Multiple table with the same primary key (design issue)

I would say to stick with the one table idea. The problem with splitting it into multiple tables is two fold.

1. Queries will take longer. You will have to join your tables to get the results you want. Data will be distributed along multiple datablocks, causing multiple reads and so forth.
2. Data integrity (your prime objective) is much more difficult to insure.) Your primary key will limit the row in a table to a unique value. If you have your data is represented in multiple tables, you will have to get fancy to ensure that you have a unique key.

What you want to look at is something called data normalization. You can find this subject in any database book that deals with relational design. The concept is this, you may have columns in your "master table" that can be broken out into other tables. You will want your master table to access these tables via something called a foreign key. This foreign key will reference the primary key of the other tables.

A good candidate for normalization is a table that has columns in it that can be determined by other columns within this table. I hope this helps you. If you could post the column names or describe the columns, I could tell you the best way to set up the tables. Definitely do not break out your primary key into multiple tables.

John

<wtgr_at_jhl.co.th> wrote in message
news:c73b7a7e.0204021107.741a1d0d_at_posting.google.com...
> hi
> i am new to database design and facing a problem.
>
> is it better to have a table with 20 column and a primary key
> "USER_ID",
> or have three smaller tables each with the same primary key "USER_ID"?
>
> ie.
>
> 1st choice
> table A
> primary key USER_ID
> column USER_ID 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
>
> or
> 2nd choice 3 tables
> table B.1
> primary key USER_ID
> column USER_ID 1 2 3 4 5 6 7
>
> table B.2
> primary key USER_ID
> column USER_ID 8 9 10 11 12 13 14
>
> table B.3
> primary key USER_ID
> column USER_ID 15 16 17 18 19 20
>
>
> i would like to know which choice is better for the performance?,
> issues with indexing?, any pros and cons?
>
> the database i use is oracle 8i and 9i on solaris and linux
>
> thank you very much
Received on Tue Apr 02 2002 - 23:11:56 CEST