I have a table person which I am planing to normalize to the sixth normal form.
The table has attributes username, email, and 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_username, person_email, 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:
| person_id | username | valid_from |
|---|---|---|
| 1 | foo | 2022-01-01 |
| 1 | bar | 2021-06-01 |
| 2 | foo | 2020-08-01 |
| 2 | abc | 2020-02-01 |
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 username 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 EXCLUDE constraint?
