Contact Manager

part of the ArsDigita Community System by Philip Greenspun

User-accessible directory: /contact-manager/
Site administrator directory: none (only site admin activity is to put people in the relevant user group)
data model : /doc/sql/contact-manager.sql

The Big Idea

As of March 1999, about six ArsDigita clients had come to us with the same problem

they have a big list of all the entities that they'd like to have as customers
they have a bunch of people whose job it is to contact these entities and exhort them to become customers; these people use a desktop contact manager such as ACT
when the entity is converted into a customer, information about it must be rekeyed from ACT into the main Oracle database that sits behind their Web site

All said "we don't really care if we lose 95% of the features of ACT; we want to drive all of our activities from one database."

So here is a system with 5% of the features of ACT. It has one killer feature, though: you can tie it to the Oracle table of your choice!

Stuff we need to keep in the /parameters/ad.ini file

The Contact Manager system needs to know

to what table am I tied?
what column holds the primary key?
what data type is this column ("text" or "number"; we just want to know whether we have to wrap values in '' or not)
what column holds the best short name for an entity?
singular noun for an entity
plural noun for entities
how to do a transaction saying "this org is now signed up; they don't need to be sold anymore" (the parameter is the name of a PL/SQL proc to exec with an argument of the primary key value of the relevant entity)
how to do a transaction saying "this org isn't worth signing up; they don't need to be contacted anymore" (the parameter is the name of a PL/SQL proc to exec with an argument of the primary key value)
what goes after an SQL ORDER BY if we want to rank entities by descending order of necessity to contact (e.g., for potential customers of a bank's VISA card program, you'd have bankrupt_p, income desc (rank by bankrupt or not first, then by descending income.))

Stuff we store in Oracle tables

The one thing that saddens me is that it is going to be impossible to spec a referential integrity constraint in the .sql file because we don't know in advance to what table the contact manager will be tied. However, we can say that we want to keep

date of contact
who did the contacting (we assume it is person logged into browser; i.e., that people record their own contacts)
Name of contactee
email address of contactee
free text note (4000-character long max; not worth using a CLOB)

We don't try to keep any fancy structure for things like the phone number of the contactee. This might be annoying for users and it is useless because the only thing that this software is really be able to do with structured info is sent email (so we want the name for a "Dear Foobar" salutation and the email address for the To: header).

philg@mit.edu