Contact data model

[Paul Fisher]

I've included a copy of the data model of the contact management
portion of our database.  This data model was started before I became
aware of DCL, and it's recently been finished, in coordination with
finishing up the other parts of the database (ordering, donations)
that hook into it.

I haven't yet attempted to merge the DCL data model for contacts with
this data model, but that's something that needs to be done.

All the items in our data model are needed to accurately represent the
current data in our transactions database (kaos), and represent the
information that our Director of Development needs.

The size of varchar fields is somewhat arbitrary and can be mostly
ignored in the xml file attached below.  Please also ignore formatting
and grammar errors in the xml file -- it was hand typed, and I haven't
attempted to parse it. :) The xml file was generated by hand from a
giant dia file that I've been using to create all these related
databases.

Brief description of the data model:

A person is represented by an entity_id.  A person can hold n
positions, where each position is at a firm.  A firm represents
departments, divisions, and companies -- each firm can have a parent
firm.  For example the Engineering Dept of Red Hat would have a firm
record, and its parent firm would be Red Hat.

Persons can have n postal addresses.  Persons and firms can have n
urls, phone numbers, and email addresses.  Firms can only have one
postal address.  For addresses references, either us_address_id or
intl_address_id must be null -- this tells us which table to look up
an address in.

The format for postal addressing is taken from USPS Publication 28.
Pub28 defines standard business to business fields such as functional
title, professional title, and mail stop codes.  All US mail,
excluding Puerto Rico, only needs a single delivery line -- all other
delivery information comes associated firm, position, and person
records.

International addresses are represented with three delivery lines, in
addition to city, region, postal code, and country.  We have numerous
international addresses in our current kaos database with foreign
addresses containing three delivery lines.

Finally there are two tables which allow for combined, personalized
greetings.  For example, if Robert and Sue live together, and we want
to send them a single letter, we can address them as "Bob and Sue".

What I suppose needs to be done to combine DCL's contact management
data model with this one is to take what items would be generally
useful for DCL and the average contact management system and put them
into DCL, and keep those items that are specific to the FSF in
separate tables.

contact-table.xml
Description: Text document