Re: Database design question: ugliness or referential integrity? - Mailing list pgsql-general

If I was doing this, I'd make a table:
email_event_log:
email_address
event
who_did_it
datestamp

Then you can make events be logged when the happen.  Events: ADD, DELETE,
PASSWORD, etc.  Make it so that only legal events are valid in the events
column for consistency and you are good to go.  Keep the PHB's happy!

Easy to generate reports and find all that has happened on a given email
address, etc.

Scott

On Wednesday 29 October 2003 09:38, Paulo Jan wrote:
> Hi all:
>
>     Let's say I'm designing a database (Postgres 7.3) with a list of all
> email accounts in a certain server:
>
>
> CREATE TABLE emails (
> clienteid INT4,
> direccion VARCHAR(512) PRIMARY KEY,
> login varchar(128) NOT NULL,
> password VARCHAR(128),
> dominio VARCHAR(256)
> );
>
>
>     The PHBs want to have a log of when was an email account added, which
> technician did it, when was it deleted, when did we have to reset its
> password, etc.:
>
>
> CREATE TABLE emails_log (
> direccion varchar(512) references emails,
> fecha date,
> autor varchar(32),
> texto varchar(1024)
> );
>
>     "texto" would be a free form text field explaining what has been done.
>     Now, let's suppose that an email account is deleted, and six months
> later another user requests it and we add it again. Do we want to keep
> an audit trail for the old "version" of that account? The PHBs say yes.
> Which means that we can't use the email address as primary key. Fine, we
> add an "ID" column to the "emails" table and make it the primary key,
> and point the foreign key in "emails_log" to that column. But now we
> have two options, and here is my question:
>
>     -In "emails", the "direccion" column needs to be unique... but only for
> the active email addresses (there can be 5, 10, or 20 dead addresses
> called "luser@domain2.com", but only one alive at the moment). We could
> add an "active" boolean column to "emails", and write a custom
> constraint to check this condition, but I find it ugly (and I saw
> similar objections when another user came up with a similar problem some
> time ago)...
>     -...Or we could create a table called "dead_emails", and add to it the
> email addresses that we delete (using an ON DELETE trigger, perhaps).
> Basically, store the deleted email accounts in another table... but then
> we lose the referential integrity check in "emails_log".
>
>     The question is: what would you do? (I don't really like the idea of
> creating yet another "dead_emails_log" table pointing to "dead_emails";
> I find it almost as ugly as the first one).
>
>
>
>                     Paulo Jan.
>                     DDnet.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend