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

Thank you very much for the reply. It's seem that my database does not have normalization (that's why i post the original question). The current database has three table which i want to redesign to combine them into one table. The tables are like

User_id (primary key)
password
passphrase
passphrase_hint
last_login
fail_login
status

User_id (primary key)
First_name
middle_name
Last_name
street
city
zip
country
email
phone
fax

User_id (primary key) (a user only allow one bank)
bank_name
bank_phone

User_id is the foreign key to some other tables like transaction_table, customer_catagory_table, etc...

thank you very much again

"news" <x_at_y.com> wrote in message news:<w8pq8.105686$u77.26306750_at_news02.optonline.net>...
> 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 Wed Apr 03 2002 - 19:32:00 CEST