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

"news" <x_at_y.com> wrote in message news:<_ZHq8.121677$u77.28838732_at_news02.optonline.net>...
> I would recommend combining all 3 tables into your master table (users).
> Assuming that a user can have only one address. If this is not the case,
> you will need to separate the address portion out and make a decision of how
> you want to store that information. I won't get into it unless this is the
> case. I may become confusing. Than being said, the one table is the way to
> go. It will make queries easier to read, write and quicker to execute. It
> will reduce the redundancy of your user_id repeated 3 times for every user,
> and it will make data integrity much easier to manage. Like I said earlier,
> if address is one to one with user, as bank is, this is the way to go.
> Repost otherwise.
>
> John
>
> <wtgr_at_jhl.co.th> wrote in message
> news:c73b7a7e.0204030932.63e140d9_at_posting.google.com...
> > 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
> > account_number
> > route_number
> > credit_limit
> > fail_transactions
> >
> > 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

Hey,
Why do you think that you need to keep user_id as a primary key in every table. you can use user_id as a foreign key in other table.

hoque Received on Thu Apr 04 2002 - 09:59:50 CEST