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

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
Received on Wed Apr 03 2002 - 20:37:46 CEST