Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

1 Answer
1

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.

Thanks for the answer. If you don't mind me asking, how might you model a similar situation without the party model? Also, why is it that you prefer not go with this approach?
–
funseikiNov 1 '13 at 17:38

I see your point, but with larger polymorphic associations, though, I feel like the n:n relationship argument becomes more valid. For example: If I had users and organizations, and I also had emails, notes, and private messages where users or organizations could create and send these to other users or organizations - I now need to create relationship tables for every possible type of relation: E.g. email:[user_send, org_receive], email:[org_send, user_receive] etc. This seems like it too could be cause for confusion. Thoughts?
–
funseikiNov 11 '13 at 20:33