Thread: design
I have a design question. Lets say we want to keep track of users and their respective snail mail addresses. Each user can have up to 4 different mailing address. Is it better to have all this information in one table. Or is it better to have a user table and an address table, and have the user id as a foreign key in the address table? Thanks! Jeff
Depends on your needs. Typically I would always break out the addresses into another table, since it's much more flexible. creating four separate address fields in the user table will reduce the need to perform joins and thus perhaps make your schemea a little simpler, and your queries a little faster. However if users typically have less then four addresses then this is not efficient either. > -----Original Message----- > From: Jeff [SMTP:jeff4e@rochester.rr.com] > Sent: Tuesday, January 30, 2001 10:35 AM > To: pgsql-general@postgresql.org > Subject: [GENERAL] design > > I have a design question. Lets say we want to keep track of users and > their respective snail mail addresses. Each user can have up to 4 > different mailing address. Is it better to have all this information in > one table. Or is it better to have a user table and an address table, > and have the user id as a foreign key in the address table? > > Thanks! > > Jeff
On Tue, 30 Jan 2001, Jeff wrote:
> I have a design question. Lets say we want to keep track of users and
> their respective snail mail addresses. Each user can have up to 4
> different mailing address. Is it better to have all this information in
> one table. Or is it better to have a user table and an address table,
> and have the user id as a foreign key in the address table?
I would put the addresses in a separate table and use the foreign key.
That way each user can have as many addresses as you want. A year from
now you might change your requirement to 5 addresses. Or perhaps you want
to keep historical information.
In general, if you find yourself designing a table where duplicate
information is showing up (in this case, if you had only used one table,
user names would have been entered 4 times, once for each address), you
need to apply normalization and break it into two (or more) tables.
-- Brett
http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
Health is merely the slowest possible rate at which one can die.
> Each user can have up to 4 > different mailing address. Is it better to have all this information in > one table. Or is it better to have a user table and an address table, > and have the user id as a foreign key in the address table? It's even possible (recommended by the books) to have separate tables for streets, cities, states, countries etc. However, normalisation up to, say, 4NF ain't easy to implement and support. I'd also apprectiate someone sharing real life experience. As to your particular problem, I'd definitely start with having users separated from addresses. Especially if the database would complicate and other user info is likely to be stored in your DB. -- ������������������
Plus, it allows you to add more functionality later. Say you later want to keep track of phone numbers. Add another table with a phone number field linked to the master table with the users instead of making your one table consistently larger. Adam Lang Systems Engineer Rutgers Casualty Insurance Company http://www.rutgersinsurance.com ----- Original Message ----- From: "Brett W. McCoy" <bmccoy@chapelperilous.net> To: "Jeff" <jeff4e@rochester.rr.com> Cc: <pgsql-general@postgresql.org> Sent: Tuesday, January 30, 2001 11:51 AM Subject: Re: [GENERAL] design > On Tue, 30 Jan 2001, Jeff wrote: > > > I have a design question. Lets say we want to keep track of users and > > their respective snail mail addresses. Each user can have up to 4 > > different mailing address. Is it better to have all this information in > > one table. Or is it better to have a user table and an address table, > > and have the user id as a foreign key in the address table? > > I would put the addresses in a separate table and use the foreign key. > That way each user can have as many addresses as you want. A year from > now you might change your requirement to 5 addresses. Or perhaps you want > to keep historical information. > > In general, if you find yourself designing a table where duplicate > information is showing up (in this case, if you had only used one table, > user names would have been entered 4 times, once for each address), you > need to apply normalization and break it into two (or more) tables. > > -- Brett > http://www.chapelperilous.net/~bmccoy/ > -------------------------------------------------------------------------- - > Health is merely the slowest possible rate at which one can die.
On Tue, 30 Jan 2001, Jeff wrote: > I have a design question. Lets say we want to keep track of users and > their respective snail mail addresses. Each user can have up to 4 > different mailing address. Is it better to have all this information in > one table. Only if you have mostly 4 address users. And then it questionable. > Or is it better to have a user table and an address table, > and have the user id as a foreign key in the address table? Yes! Though at first reading I thought you had your primary key and foreign key reversed. Primary key user_id (???) in the user table. Foreign key references user(user_id) in the address table. You might want to think of an ordering attribute (column) in the address table so you can select a preferred address when only on is wanted. Have a normalized day, Rod --
On Tue, 30 Jan 2001 11:35:21 -0500, Jeff <jeff4e@rochester.rr.com> wrote: >I have a design question. Lets say we want to keep track of users and >their respective snail mail addresses. Each user can have up to 4 >different mailing address. Is it better to have all this information in >one table. Or is it better to have a user table and an address table, >and have the user id as a foreign key in the address table? > >Thanks! > >Jeff > The second option sounds much better to me. Imagine trying to find out how many addresses each user has: With everything in one table... tough one. With all addresses in their own table and keyed by user id... simple. I am sure you can find other reasons too. Lee Harr missive@hotmail.com