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.