0

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.

1 Answer 1

2

"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).

5
  • Do I understand you correctly that this would also require triggers, right? I mean, such a consumer view which would be based on a new proper consumer table joined with a person table, would typically not be fully updateable, unless we use triggers to support that.
    – Magnus
    Jun 5 at 15:02
  • The "instead of" trigger is available for views, in fact it is only available for views. Which is interesting, I was namely considering using an "instead of" trigger for the solution I have described, but I found that "instead of" triggers can only be defined for views. So then your idea is interesting from that perspective.
    – Magnus
    Jun 5 at 15:08
  • 1
    @Magnus: "Do I understand you correctly that this would also require triggers, right? " Yes, it would require triggers. In this case, triggers are part of the technical debt that you could eventually remove. Jun 6 at 11:10
  • I have looked at the solution you suggest, i.e. replacing table "consumer" with a view named "consumer" (with joins and triggers). As far as I understand, this means I have to rename the base table (to for example consumer_base). But if I rename a table, then all dependent objects (such as views) still refer to the same table, with its new name. I could of course drop all such objects and recreate them so that they instead refer to the new view "consumer", but that might be a lot of work, again a bit error prone. Do you know of a smart solution for this problem?
    – Magnus
    Jun 16 at 9:29
  • 1
    @Magnus: Replacing a table with a view (and vice versa) is a common database refactoring. As with other kinds of refactorings, the work is less risky if a) the database code (SQL) and the application code is under version control, and b) you have a robust test suite. This can be a lot of work. It might be worth it. It might not. Jun 21 at 10:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.