Re: Simple db table design question

From: Tim X <timx_at_spamto.devnul.com>
Date: 25 Mar 2003 18:41:44 +1100
Message-ID: <8765q77v0n.fsf@tiger.rapttech.com.au>

>>>>> "Jon" == Jon Maz <jonmaz_at_surfeu.de.no.spam> writes:

 Jon> Hi, For my tblPeople, I want fields for HomeEmail, WorkEmail,
 Jon> and BestEmail, where BestEmail is the default address you use to
 Jon> contact the person (in this simplified scenario, either
 Jon> HomeEmail or WorkEmail).  I am trying to decide between a few
 Jon> ways of doing this.


 Jon> METHOD 1 tblPeople just has 3 VarChar fields, each containing
 Jon> email addresses - clearly BestEmail will be a duplicate of one

Jon> of the other two, which I have a feeling is "bad db design Jon> practice".

 Jon> METHOD 2 HomeEmail and WorkEmail are VarChar fields containing
 Jon> email addresses BestEmail is a Varchar field containing "Home"
 Jon> or "Work"

 Jon> METHOD 3 A separate tblEmails holds all email addresses

Jon> tblPeople has 3 bit fields HomeEmailID, WorkEmailID, and Jon> BestEmailID, pointing to the PK field of tblEmails.

 Jon> There are probably some other ways of doing this as well.  Does
 Jon> anyone have any advice on the pros and cons of the different
 Jon> methods, and in which situation one method or other is
 Jon> preferable?

I would go with some variant of method 3. Certainly avoid methods 1 and 2 as they have mixtures of bad practice and would create apps which are difficult to extend and maintain.

I have done something similar to what you are doing, but with some additional requirements. In the app I developed, we had a table of email addresses which contained fields for the address, the primary id of the person the address belonged to, a code indicating the type of address (Home, work, temporary, etc) and a rating which indicated its preference. We needed all these fields because the domain we were dealing with involved people who changed locations and often e-mail addresses depending on the time of year.

The advantages of putting the addresses in a table of its own with indication of type and preference are

Easy to add new address types if needed
Could easily keep a history of addresses (defunct addresses could have a null or some other indicator in the pref field).
No limit on the number of addresses for an individual

Tim

-- 
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you 
really need to send mail, you should be able to work it out!

Received on Tue Mar 25 2003 - 01:41:44 CST