0

In trying to solve a problem I was having with having different types of the same table, I decided to go with the Party Model design for relational databases - using multiple tables to represent the sub-objects.

Using this post as well as this one as guides, I decided to construct a Party model using multiple table inheritance.

Here is a basic representation of what I'm doing (in pseudo sql):

Parties
- ID, AUTO_INCREMENT
- Type
- Shared_Data
- Primary Key (ID, Type)

User_Sub
- ID
- Type, Default 'U'
- User_Data
- Primary Key (ID, Type)
- Foreign Key 'User-Party' (ID, Type) references (Parties.ID, Parties.Type)

Organization_Sub
- ID
- Type, Default 'O'
- Organization_Data
- Primary Key (ID, Type)
- Foreign Key 'Organization-Party' (ID, Type) references (Parties.ID, Parties.Type)

Because data is spread across multiple tables, I'd like to be able to read from and update to the parent and child tables at once. Reading is fine, and can be done with a: 

CREATE VIEW Users as
SELECT P.*, U.User_Data FROM Parties P 
Inner Join Users_Sub U on P.id=U.id, P.type=U.type

But, inserting into both is not possible in MySQL (is it possible in other flavors?). Is it not possible to do multiple-table inheritance in such a manner? Putting all my User and Organization Columns into the Parties Table would probably solve this issue, but I'd be left with quite a few null fields.

Insight on this would be much appreciated.

Community
  • 1
  • 1
funseiki
  • 9,167
  • 9
  • 36
  • 59

1 Answers1

1

MySQL supports multi-table updates and deletes, but not inserts. Nor does MySQL support INSTEAD OF triggers like SQL Server does (which would provide one workaround for the lack of multi-table inserts).

To deal with this, one option would be to create a stored procedure for your single-row inserts, which starts a transaction, inserts into Parties, grabs the new ID value (using LAST_INSERT_ID()), then inserts into the child table.

Your decision may ultimately depend on whether you need to perform multi-row inserts on a regular basis. If so, if you had all the unique child table columns also present (and nullable) on the parent (Parties) table, you could use an AFTER INSERT trigger to insert the non-Parties-specific column values into a new row in the appropriate child table.

Another less elegant (and less efficient) scenario would be (in a stored procedure) to lock the Parties table, save the highest auto-increment ID to a variable, perform the multi-row Parties insert, save the last-inserted ID (now the highest ID), then insert rows into the appropriate child table using an incrementing variable for ID (to match the range of the IDs you just created in Parties).

udog
  • 1,490
  • 2
  • 17
  • 30
  • Hmm, so it seems like these are all workarounds. Does multi-table inheritance not actually happen in practice? As far as one of the current options, going with a stored procedure or just doing two queries seem like the only viable things to do. I wouldn't be able to use a trigger, because the data wouldn't exist in the parties table. – funseiki Nov 01 '13 at 04:26
  • This relationship type is certainly used. It is actually 4th normal form. – udog Nov 01 '13 at 04:54
  • Is it generally implemented with Single Table Inheritance? Your trigger suggestion would require having all of the child columns exist in the parent table. Doing so introduces redundancy - I could get rid of the child tables altogether. Unless there's something I'm missing. – funseiki Nov 01 '13 at 16:12
  • You are correct, it cannot be done with a trigger without having the redundant columns on Parties. My experience with this implementation is that multi-row inserts are simply not used with this relationship design. If your actual table schema is as simple as the design you posted (meaning, the child tables don't vary much from Parties), then it makes sense to keep it one table. You will need to frequently check the Type code, but that is probably not a big deal. I recommend you go with the design that you feel is most maintainable. – udog Nov 01 '13 at 21:04