Comments on: SQL and simple polymorphism

By: David Megginson

David Megginson — Sun, 11 Mar 2018 03:11:58 +0000

In reply to Ribaldos. Wouldn't that just be a different implementation strategy for the same concept?

By: Ribaldos

Ribaldos — Sat, 10 Mar 2018 15:32:50 +0000

This is no polymorphism. It would be when “Locations” had a (virtual) column which is dispatched to different implementations (e.g. retrieving columns from the child table).

By: Chris Hennick

Chris Hennick — Sun, 20 Jul 2014 15:50:21 +0000

The only problem with this is that a clueless new employee might add a place with no subtype, or with more than one subtype.

By: John Cowan

John Cowan — Sun, 26 Sep 2010 17:49:50 +0000

If your database unique IDs are just incrementing numbers, then yes. If they are truly unique (e.g. UUIDs), then you can safely create the derived table entry before the base one.

By: stand

stand — Sun, 26 Sep 2010 05:47:08 +0000

In reply to davidmegginson. Yes, I am assuming that the id is an input parameter to the insert operations. You'd have to use a GUID or some other id generating strategy.

By: davidmegginson

davidmegginson — Sun, 26 Sep 2010 01:41:06 +0000

In reply to stand. stand: interesting idea, but if you want a unique id across all the subtypes, don't you have to assign it in the base table first? Of course, if you already have your primary keys (e.g. employee numbers), then it's not an issue.

By: stand

stand — Sun, 26 Sep 2010 01:10:00 +0000

Another trick a friend clued me into for inserts. Insert into the base table last. This gives you an effective transaction across all your base and derived tables inserts because if you insert into a derived table and then fail to insert to the base table, you just have an orphaned record in the derived table which won’t show up in your normal queries. You can be assured that once the base table insert completes, you have a full record. Also, from a concurrency standpoint, your record doesn’t show up to other reading threads until the base record is inserted and the full object can be populated.

For deletes, you remove the base table record first and then clean up the orphaned derived records.

By: Francis Avila

Francis Avila — Sat, 25 Sep 2010 23:17:58 +0000

This technique is also called "joined table inheritance" and I've used it to good effect in fairly large projects. Unfortunately it's very awkward to use with most ORM systems since it breaks the active record/active table pattern of one class per table and one row class per table row. SQLAlchemy (python library) has native support for this pattern (and two other sql "inheritance" patterns) and makes working with them a dream. I constantly long for something of SQLAlchemy's expressiveness and power for php (which is 90% of my day job), but all the php ORMs (such as they are) appear to be active record patterned as well, vs the data mapper pattern SQLAlchemy uses where "objects" in your app are decoupled from tables, and you can establish flexible mappings between the two.