schema enhancements

Hi all,

I was away all last week, and had some airplane time to think about the schema
that we've been working on. Here's what I came up with. Please let me know
what you think...

----------------------------------------------------------------------

-- Each person in our address book has one of these records.
create table gpe_person (
  person_id integer primary key,
  first_name text,
  last_name text
  )

-- The enumerated contact types. We will have a set of pre-defined ones (e.g.
-- "home phone", "work email", etc. but the user may add as many as he likes.
-- By convention, the pre-defined ones will have the contact_type_id value
-- less than 1000. User-added ones will be 1000 and greater.
create table gpe_contact_type (
  contact_type_id integer primary key,
  contact_type_name text
  )

-- Each contact associated with a person has one of these records. (This is
-- a many-to-many relationship so there is no primary key.)
create table gpe_contact (
  person_id integer not null references gpe_person,
  contact_type_id integer not null references gpe_contact_type
  )

-- A person may belong to various groups that indicate where we know this
-- person from. For example, we may know him from Boy Scouts or church.
-- Similarly, a user can set up a group for everyone involved in a particular
-- meeting at work, to easily access that whole group of people.
create table gpe_social_group (
  social_group_id integer primary key,
  social_group_name
  )

-- The association table which links a person to a group. Any person may be
-- placed in multiple groups, by simply adding an addition record of this type
-- with the same person_id but a different social_association_id. (This is a
-- many-to-many relationship so there is no primary key.)
create table gpe_social_association (
  social_association_id integer not null references gpe_social_group,
  person_id integer not null references gpe_person
  )

-- Just as with contacts, there are various types of addresses (e.g. home
-- address, work address, etc.) We will provide a number of pre-defined
-- address types (numbered <1000) and the user may add their own (>=1000).
create table gpe_address_type (
  address_type_id integer primary key,
  address_type_name text
  )

-- An address for a person. There may be multiple of these for a particular
-- person and there may even be multiple of these for the same person and
-- address type, e.g. multiple work addresses. By convention, the first
-- address of a particular type is considered the "primary" one.
create table gpe_address (
  person_id integer not null references gpe_person,
  address_type_id integer not null references gpe_adress_type,
  address_line_1 text,
  address_line_2 text,
  address_line_3 text, -- may be used for non-US addresses where
  address_line_4 text, -- city/state/zip doesn't apply
  city text,
  state text,
  zip text,
  country text
  )

-- Events may be simple alarms, scheduled activities, or tasks. If there is
-- an associated person or group (e.g. the event is an activity associated
-- with an engineering meeting), then that association may be represented in
-- associated_person or associated_group, allowing for easy
-- click-for-information on the association.
create table gpe_event (
  event_id integer primary key,
  event_type integer, -- 1=alarm, 2=activity, 3=task
  event_name text,
  exclusive_p boolean, -- no other event to be scheduled at same time
  priority integer, -- 1=high, 2=medium, 3=low
  start_time datetime,
  end_time datetime,
  visible_time datetime, -- when to begin displaying this event
  high_priority_time datetime, -- when this event changes to high priority
  alarm_time datetime,
  associated_person integer references gpe_person, -- may be null
  associated_group integer references gpe_social_group -- may be null
  )
Received on Tue Feb 26 22:14:51 2002