Re: Simple db table design question

Jon Maz wrote:
> 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.
>
> METHOD 1
> 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".

Correct (about bad practice)

> METHOD 2
> HomeEmail and WorkEmail are VarChar fields containing email addresses
> BestEmail is a Varchar field containing "Home" or "Work"
>
> METHOD 3
> 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?

METHOD 4
All the three methods you are suggesting are difficult to maintain (what will happen if you decide to add VacationEmail) and will make certain queries complicated (for example, you'll have to use UNIONs in order to find all the emails of all the people)... So, my suggestion is (assuming that one mail is owned by at most one 'people'):

CREATE TABLE tblPeople (

	PeopleID INTEGER NOT NULL PRIMARY KEY,
	...

	EmailID INTEGER NOT NULL PRIMARY KEY,
	Eaddress VARCHAR(??) NOT NULL,
	Etype ???,
	...
	PeopleID INTEGER NOT NULL REFERENCES tblPeople(PeopleID)

	EmailID INTEGER NOT NULL PRIMARY KEY,
	PeopleID INTEGER NOT NULL UNIQUE,
	CONSTRAINT fk_b_e FOREIGN KEY (EmailID, PeopleID)
	REFERENCES Email(EmailID, PeopleID)

Note that attribute PeopleID in relation BestEmail is not necessary, but   it is now easier to enforce integrity rule that one 'people' has at most one best email and also you'll need to join 2 tables to find best email for a given 'people' (instead of 3).

Finally, your naming conventions look very strange to me.

-- 
Regards,
Damjan S. Vujnovic

University of Belgrade
School of Electrical Engineering
Department of Computer Engineering & Informatics
Belgrade, Serbia

http://galeb.etf.bg.ac.yu/~damjan/