Normalizing an old database

Question

I have a need to normalize an old PostgreSQL database, and I have an idea on how to do it which I would like to hear your opinions about.

The aspect that needs to be fixed is this:

We have a consumer table. It has the basic columns of a person table. Let's say for the sake of simplicity that these are just the "not null" columns firstname and lastname. It also has a bunch of other columns that do not belong in a person table. We now need to introduce a real person table, which the consumer table references with a foreign key.

The ideal fix would simply be to remove the columns firstname and lastname from the consumer table, and add a foreign key referencing a new (real) person table (with the columns firstname and lastname, and some other columns).

The problem is that the system around this database is old, and lots of code and queries do things with the consumer table, which makes it troublesome to remove the columns firstname, lastname from it. We want to avoid that, since we would then need to reprogram big parts of the system, which would also be error prone.

I have an idea though on how to fix this so that there will be no need to change any existing coding etc., but so that we will still have a real person table which our existing consumer table references, as it should be.

My idea is based on having two triggers and an "on update cascade", as follows:

I create a person table with the primary key person_id (uuid). It also has "not null" columns firstname and lastname, and some other columns.

In the person table I also create a unique composite key consisting of person_id, firstname, and lastname.

I let the consumer table remain exactly as it is, apart from:

• Adding the column person_id.
• Adding a composite foreign key consisting of person_id, firstname, and lastname, referencing the corresponding composite key in the person table. This composite foreign key has "on update cascade", so that if any of those columns changes in the person table, the corresponding column in the consumer table will reflect that change.

I add a "before insert" trigger to the consumer table, which calls a function that prepares the person table by inserting a row with the corresponding person_id, firstname, and lastname into it.

I add a "before update" trigger to the consumer table, which calls a function that prepares the person table by updating firstname, and lastname in its corresponding row.

There will be some recursion happening because of these triggers and "on update cascade", but I have found a way to deal with that.

The result is this:

The consumer table will work and behave exactly as before from the perspective of all the existing code and queries (even update and insert queries) in the system.

The consumer table will work as a table referencing the person table, with full referential integrity.

I would like to hear your opinions about this solution, and of course if you have some alternative ideas for how to solve this.

Answer 1

"Require no application code changes" is the right way to go at this stage.

Another alternative is to fully normalize the consumer table, which would remove the columns "firstname" and "lastname" from it, and replace the base table "consumer" with a view named "consumer". Use whatever joins and triggers are necessary to make that view "consumer" behave exactly like the old base table "consumer".

But this still amounts to "technical debt". Consider planning to update other code to use the new tables, or changing the architecture so all code must use views (no direct access to base tables).