Mysql – Party Relationship Model in Practice – Insertions

insertMySQL

I'm moving this question for StackOverflow in hopes to get perspective from a more database-oriented community.

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 insert into 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

Inserting, though, is more difficult.

  • I can do two consecutive queries: One into the parties table, then one into a sub table.
  • I can create a stored procedure in order to perform multiple queries in one transaction.
  • This answer mentioned moving columns into the super table and using a trigger after insert on the super table to insert into the sub table.

It seems to me that it might be more fitting to consolidate these sub-table columns back into the super-table and get rid of the sub-tables entirely. Since I'd probably need to join or union all of the subtables if I wanted to query values from the parties table anyway.

Is this generally how it is done in practice? There will be many unused columns if this is the case, and it will be difficult (though not impossible with triggers) to enforce 'not null' constraints. The case above shows only two sub table types – as the number of sub-types increase, the super table will become more bloated.

Any thoughts or suggestions on this would be much appreciated.

Best Answer

How about another answer? Get rid of the Party table entirely. I'm a passionate hater of the Party model as it causes vastly more problems than it solves.

However, since it's unlikely you're in the position to do such a thing:

I can do two consecutive queries: One into the parties table, then one into a sub table.

There's nothing per-se wrong with this as long as you do your transaction control in your app. It's not what I would suggest though.

This answer mentioned moving columns into the super table and using a trigger after insert on the super table to insert into the sub table.

This is nasty from a model perspective. If you go this route you have attributes that apply only to organizations intermingled with attributes that are only for users. It becomes quite a mess and you just 'have to know' what is right and wrong. It is a significant impediment to clarity.

I can create a stored procedure in order to perform multiple queries in one transaction.

This is your best option. It maintains atomicity while at the same time encapsulating and decoupling the data access from your app. Do this.

UPDATED:

To clarify my point about the Party model. The following are my major beefs with the approach:

  • There is no such thing as Party. That is, there is no natural key that identifies a 'Party' in real life. As such, it is a meaningless abstraction that leads to ...
  • Models inevitably base everything off Party. Foreign keys always back to Party rather than the 'subclasses' (that aren't really subclasses). The perception is that this makes matters simpler because it avoids n:n relationships along with intersection tables and so forth. However, it ends up completely hiding what the model actually is. Some things are related ONLY to User and some things are related ONLY to Organization. Nothing is ever related to a 'Party', in reality. So it is a physical 'optimization' that results in extreme logical confusion. It becomes completely impossible to determine the model by simply reading it. You just simply have to know that certain things are allowed and certain things aren't. Inevitably this ends up in model confusion, code mistakes/complexity, report inaccuracies, etc. It also leads to other evils like not taking keys seriously.
Related Question