Re: Simple db table design question
Date: 25 Mar 2003 18:41:44 +1100
Message-ID: <8765q77v0n.fsf_at_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 oneJon> 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 addressesJon> 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 - 08:41:44 CET