Class Table Inheritance (CTI) and Insert Select statements

Question

I am working to build an inheritance database model within MySQL, such that all tables inherit from one base type (object), represented by the object table. This allows for Notes to be linked to any object from any table within the database while retaining referential integrity. The design looks something like this (there are a lot more child tables with similar structures):

CREATE TABLE object
(
object_id INT(10) AUTO_INCREMENT,
object_type VARCHAR(80),
PRIMARY KEY (object_id)
);

CREATE TABLE person
(
person_id INT(10),
name_first VARCHAR(80),
name_last VARCHAR(80),
email_address VARCHAR(80),
PRIMARY KEY (person_id),
CONSTRAINT fk_person FOREIGN KEY (person_id)
REFERENCES object (object_id)
);

CREATE TABLE note
(
note_id INT(10),
not_text TEXT,
note_subject_id INT(10),
PRIMARY KEY (note_id),
CONSTRAINT fk_note FOREIGN KEY (note_id)
REFERENCES object (object_id),
CONSTRAINT fk_note_subject FOREIGN KEY (note_subject_id)
REFERENCES object (object_id)
);

With this design, I am able to make a note with a person as the subject, a note with another note as the subject, or a note with one of the many other tables inheriting from object as a subject (these tables are not listed for brevity). Although it cannot be enforced through referential integrity, a presupposition of this design is that each object_id is used in only one row of one child table, so that there are no notes where the note_id is also a person_id.

The problem occurs when I want to perform INSERT... SELECT statement on person or note. Let's say that I have a user table and I would like to insert all users in to person. First I have to insert the number of new person rows I am creating into object, then I need to insert the new rows into person, but I have no way of matching each user row to an object row in order to populate the person_id column.

My first thought was to create a BEFORE INSERT TRIGGER on person that would create the new object record and update the NEW.person_id value accordingly. Unfortunately, the foreign key constraint is being evaluated before the trigger is allowed to fire, catching my orphaned row before I can correct it.

What I am looking for is either a way to change the order of constraint execution so that BEFORE INSERT triggers precede constraints, or for a more elegant way to achieve an Class Table Inheritance database structure within MySQL.

Answer 1

This is not object orientation, you just want to implement inheritance in your RDBMS. You have three choices: Horizontal Mapping, Vertical Mapping, Filtered Mapping.

Reference: http://modeling.sourceforge.net/UserGuide/design-inheritance.html

You may not need inheritance though, if you relax a little on reference integrity. Your Note table may contain multiple nullable foreign keys, one for each table you want to add the note to.