I have a table
person which I am planing to normalize to the sixth normal form.
The table has attributes
phone. The reason I want to go to the sixth normal form is keeping the change history of these columns and adding additional metadata to them (e.g. the confidence level about the data).
I am planning to create new tables
person_phone and have the
person view which brings them all together and has
INSTEAD OF triggers so that users can insert and update the view and these changes get directed to the underlying tables.
My question in, how to enforce
UNIQUE constraints when using the sixth normal form.
The structure of the
person_email table would look something like this:
CREATE TABLE person_email ( person_id integer FOREIGN KEY person(id), username text NOT NULL, valid_from datetime );
The data in the table might look like this:
The rows marked as bold hold the most recent (the current) information. Both users 1 and 2 have the current username set to
foo which should not be allowed. However, I cannot make
UNIQUE, since the fact that someone had used that name in the distant past does not mean that someone else could not use it now.
What I would need would be a
UNIQUE (username) constraint which would only look at the most recent row for each user. How to achieve that? Is there a way to make use of the PostgreSQL