Skip site navigation (1) Skip section navigation (2)

Database design question: ugliness or referential integrity?

From: Paulo Jan <admin(at)digital(dot)ddnet(dot)es>
To: pgsql-general(at)postgresql(dot)org
Subject: Database design question: ugliness or referential integrity?
Date: 2003-10-29 17:38:33
Message-ID: 3F9FFB19.4020102@digital.ddnet.es (view raw or flat)
Thread:
Lists: pgsql-general
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(at)domain2(dot)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.



Responses

pgsql-general by date

Next:From: Lynn.TilbyDate: 2003-10-29 17:42:14
Subject: Re: 7.3.5 release (was: Re: SELECT with row>32k hangs over
Previous:From: Alvaro HerreraDate: 2003-10-29 17:38:02
Subject: Re: Performace question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group