Re: Schema

Erik Bågfors <erik_at_bagfors.nu> writes:

> While we're at it why not try to be compatible with other major
> contact-software. In my world that means evolution.
> ...
> PHONE:
> business
> home
> business fax
> mobile
> assistant
> business2
> callback
> car
> company
> home2
> home fax
> isdn
> other
> other fax
> pager
> primary
> radio
> telex
> TTY/TDD
>
> (wow.. that's alot of phone-numbers.. let's not take all of them)

Yup, that's exactly why I proposed a separate table for contacts. In that
scheme, you'd have as many entries in the contacts table (for a particular
person) as you you contacts for them. You don't need to use space for each
of the above-listed phone numbers unless you actually use all of them.

Looking back at my proposed schema, let's take an example...

This was what I had proposed::

create table person (
  id int4 primary key,
  first_name varchar(32),
  last_name varchar(32),
  company varchar(64),
)

create table contact_types (
  contact_type_id int4 primary key,
  contact_type_name varchar(32) -- e.g. home phone, work email, etc.
)

create table contact (
  contact_id int4 primary key,
  person_id int4 not null references person,
  contact_type_id int4 not null references contact_types
)

Many of the fields that you suggest could be added as well. However, for the
"Phone" field, I call those "contacts" (the name could be changed if that's
confusing).

Each of the contact (phone) types that you suggested, we insert them into
contact_types.

Here we create the contact_type table. The two columns are contact_type_id
and contact_type_name:

 1 business
 2 home
 3 business fax
 4 mobile
 5 assistant
 6 business2
 7 callback
 8 car
 9 company
10 home2
11 home fax
12 isdn
13 other
14 other fax
15 pager
16 primary
17 radio
18 telex
19 TTY/TDD

Now, when we enter Joe Blow into our database, we insert him into the person
table. Let's say his 'id' in the person table is 23. Then we create as many
entries in the contacts table as we have contacts for him, using 23 as the
person_id and the number from the contact_type table that we created above as
the contact_type_id.

Email addressess can be added to the contact_type table as well, as these are
just another way to contact someone.

Cheers,

Derrell
Received on Thu Feb 14 2002 - 06:11:55 EST