We've recently been putting in the Party archetype for managing information about People/Organizations and their roles in regards to our systems. When mapping the roles we had two good choices:
- Table-per-subclass - Give each role its own table as well as having a table for the basic role data.
- Table-per-hierarchy with join tables - One table to manage all the basic role data (shared by all roles) and one table for each role that required extra data (using the join-table approach).
Our database expert(s) preferred us to choose the latter, particularly because some of our roles do not have extra information and if we'd gone for table-per-subclass we'd have given those roles their own (pretty much empty) tables.
Unfortunately the join-table approach has issues, not least that the join-tables cannot support their own components so we changed our mind and instead went for the table-per-subclass approach. The amazing thing is that one of my colleagues, Kathryn, managed to make this change and have our tests passing in no more than a couple of hours.
This is brilliant, all it required was a few changes to the mappings, creation of a couple of tables for the roles that weren't (currently) adding any data and a couple of update scripts to ensure those tables were populated and that the discriminator column was removed from the party role table.