Re: Simple db table design question

Damjan S. Vujnovic' wrote:
> Jon Maz wrote:
>
[snip]
>
> 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,
> ...
> )
>
> CREATE TABLE Email (
> EmailID INTEGER NOT NULL PRIMARY KEY,
> Eaddress VARCHAR(??) NOT NULL,
> Etype ???,
> ...
> PeopleID INTEGER NOT NULL REFERENCES tblPeople(PeopleID)
> )
>
> CREATE TABLE BestEmail (
> 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).

One small addition:
Since you want to have only one best email per person, you don't need an additional table for that. Simply include BestEmailID in tblPeople: BestEmailID INTEGER NULL,
CONSTRAINT fk_bestEmail FOREIGN KEY (BestEmailID) REFERENCES Email(EmailID),

You might then also want to add a constraint that the best email is from the person. This is not necessary with the extra table solution.

Regards,
Heinz Received on Tue Mar 25 2003 - 09:06:02 CET