Foreign keys gone wild: Blueprints and instances in a relational model

This morning I faced an interesting problem that needed a quick solution: One of our developers urgently had to add some functionality to an existing application. The problem appeared rather tedious to solve in the application code. But looking at the underlying database schema, a missing foreign key constraint was discovered, that (if put to right use) could solve everything on the database level with just a few CASCADEs and TRIGGERs.

Our original problem has nothing to do with OOP and our schema of course contains many more tables and fields. I just came up with a simplified classes and objects paradigm to present it in a more abstract way, as I think it fits quite well. That's what we basically got (only key columns shown, everything else omitted for brevity and clarity):

    +-------------+           +------------------+
    | classes     |           | class_properties |
    +-------------+           +------------------+
 +->| class_id PK |<---(4)----| class_id PK (2)  |
 |  +-------------+           | position PK (2)  |
 |                            +------------------+
 |  
(1)  
 |  +--------------+          +-------------------+
 |  | objects      |          | object_properties |
 |  +--------------+          +-------------------+
 |  | object_id PK |<--(5)----| object_id PK (3)  |
 +--| class_id     |          | position  PK (3)  |
    +--------------+          +-------------------+

We talk about classes, objects and their properties here (no methods, that's where the analogies end in our example and why I chose "blueprints and instances" instead of "classes and objects" in the title, but let's still stick with the OOP terminology here, as you're all used to it).

Our properties are special in a way, as they are strictly ordered in each and every class and object (that's why there is a position column). This and some other basic constraints were already enforced in the existing schema through FOREIGN and PRIMARY KEYs:

There were existing tools to edit classes and their properties. The developer was now faced with the task to expand these tools to not only edit the classes but to automatically adapt all the changes to already instantiated objects as well. Going through tons of existing code to identify the critical lines didn't sound like too much fun. One chance for a DBA to not always be seen in the role of the bad guy for the developers, but to jump to help and come up with something like this:

    +-------------+           +------------------+
    | classes     |           | class_properties |
    +-------------+           +------------------+
 +->| class_id PK |<---(4)----| class_id PK (2)  |<-+-+
 |  +-------------+           | position PK (2)  |<-+ |
 |                            +------------------+    |
 |                                                    |
(1)                                                  (6)
 |  +--------------+          +-------------------+   |
 |  | objects      |          | object_properties |   |
 |  +--------------+          +-------------------+   |
 |  | object_id PK |<-+-(5)-+-| object_id PK (3)  |   |
 +--| class_id     |<-+     +-| class_id          |-+-+
    +--------------+          | position  PK (3)  |-+
                              +-------------------+

Now there's one additional constraint (6): Every object can only have the properties of its class. The real benefit behind it however is not the constraint itself, but an ON UPDATE CASCADE clause associated with it. Whenever the position of a class property moves, all the objects will be changed accordingly.

A TRIGGER that fires AFTER INSERT on class_properties can make sure that every new property added to a class will be spread to its instantiated objects as well. The deletion of a class property could be handled again with an ON DELETE CASCADE clause.

The new functionality comes with a small prize though: You'll note that the table object_properties now has an additional column class_id, only needed to implement the FOREIGN KEY referring to class_properties. The content of the column itself is actually redundant, as it was already determined by the relation between objects and object_properties. To not have to set this column explicitly every time you manipulate anything in object_properties this could be done automatically by some BEFORE TRIGGERs that get the value for class_id from the objects table.

The solution seems to help and shows the niceties of CASCADE and TRIGGERs (they just saved our developer from some more hours of tedious work). But I'm still not perfectly happy with it, only due to the slight redundancy in the stored data. But that's just what I came up with at first sight.

Maybe there's another approach? Somebody solved something similar without that redundancy? I'd be interested in your comments!