[thelist] addressbook table design [sql]

[Jay Dorsey]

I'm not a database guru by any means, so if there are any database gurus
out there, please correct me if I'm wrong about what I'm saying :)

One thing I like to do in my tables that might have redundant data is
put a TYPE and STATUS field on some tables with data whose number I may
not be able to determine.

An example:

Make a table called e-mail:

ID -> foreign key to your persons.ID
EMAIL - email address
TYPE - one of a set of types that you designate.  You could have a TYPE
table, in which you could have types of WORK, HOME, SCHOOL, etc.  In
that case TYPE would be numeric, and a foreign key to the TYPE tables

Note that you could easily do the same thing with phone numbers,
addresses, etc.

I prefer this method because it doesn't force you to have empty fields
in a table -- an individual could have zero email addresses or phone
numbers, or they could have ten (I have quite a few myself for example,
for various things).  This also keeps you from needing to add columns to
the table when it's halfway filled with data.

The STATUS is just one of those fields I've always found handy to have
just in case - it's an easy way to disable a particular item without
having to remove it from the database (just in case).

hth

jay



Aleem Bawany wrote:

> I am trying to come up with a table design for an address book.
> Databases haven't been my strong point so I ask here for advice.
> I am trying to design a simple address book, and am ending up
> with a lot of fields in just two tables. Is there a better way
> to go about it and are there any fields I might be missing
> (efficiency, normalization, easy of writing queries)? I
> will be searching the databases by first or last names
> and probably by affiliation (e.g. if they went to the same
> highschool). Here are the two tables:
>
> Persons:	ID,
> 		Fname,
> 		Lname.
> 		Mname,
> 		Bday,
> 		Gender
>
> 		HomePhone1,
> 		HomePhone2,
> 		MobilePhone,
> 		Fax,
> 		PersonalEmail1,
> 		PersonalEmail2,
> 		CorporateEmail,
> 		Website
>
> 		Street1,
> 		Street2,
> 		Postal,
> 		City,
> 		Province,
> 		Country
>
> 		AcademicInstitution,
> 		GraduationDate,
> 		FieldOfStudy,
> 		CurrentProfession,
> 		Hobbies,
>
> Affiliations:	ID,
> 			MyPrimarySchool,
> 			MyHighSchool,
> 			MyUniversity,
> 			Other
>
>
>
> thanks
>
> aleem
>
> [ http://members.evolt.org/aleem/ ]
>


--
Jay Dorsey
evolt at jay dorsey dot com