Simple db table design question

From: Jon Maz <>
Date: Tue, 18 Mar 2003 16:17:43 +0100
Message-ID: <b57d62$2s2$>


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

tblPeople just has 3 VarChar fields, each containing email addresses - clearly BestEmail will be a duplicate of one of the other two, which I have a feeling is "bad db design practice".

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

A separate tblEmails holds all email addresses tblPeople has 3 bit fields HomeEmailID, WorkEmailID, and BestEmailID, pointing to the PK field of tblEmails.

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

TIA, JON Received on Tue Mar 18 2003 - 16:17:43 CET

Original text of this message