2

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.

therealmitchconnors
  • 2,732
  • 1
  • 18
  • 36
  • 3
    Lol... look brother, you tried to be too sophisticated for your own good. I would drop this implementation and just add a "notes" column in every table that I need such. – Nir Alfasi Feb 02 '12 at 18:55
  • Unfortunately, notes are not the only objects I need inheritance for. I know this is a complex model, but I didn't think Database Inheritance was so complex it should be abandoned altogether... – therealmitchconnors Feb 03 '12 at 16:36
  • so instead of inheritance you can group shared properties into a property table with two foreign keys: one that will provide the external ID of the object and another that will say in which table the object resides. – Nir Alfasi Feb 03 '12 at 17:01
  • 1
    If you are suggesting that the external ID column point to the primary key of whichever table the property is for, this would not be enforceable via referential integrity, which would cause more problems than it would solve. – therealmitchconnors Feb 07 '12 at 18:33
  • why not ? are you planning to add/remove columns for the tables ? – Nir Alfasi Feb 07 '12 at 20:10
  • referential integrity issues are irrelevant: each object continues to exists with its own table and its own unique properties, only the SHARED properties are grouped into one central properties-table. each object/table has its own id - this way you cannot confuse types of objects (as each object has a table-id and a unique object-id - so there should not be any collisions) – Nir Alfasi Feb 07 '12 at 20:23
  • I am not worried about collisions as much as I am about orphaned/invalid records due to lack of referential integrity. I have worked with databases for over six years now, and have learned through many hard lessons and outright failures never to build table relationships which cannot be enforced through referential integrity. It never ends well. Thanks for your input, though. – therealmitchconnors Feb 07 '12 at 22:39

1 Answers1

0

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.

vulkanino
  • 9,074
  • 7
  • 44
  • 71
  • I am pretty sure this design complies with Vertical Mapping in your link... Abandoning inheritance is not an option for me, as there are more than just notes that need to apply to multiple object types... – therealmitchconnors Feb 03 '12 at 16:35