I have an application that needs to log communications with users over several different mediums: Email, SMS, Voice, Website Announcements, etc.. in a traditional database.
I have considered 3 approaches to modeling these different types of data:
Single Table Inheritance:
Store them all together in a single table (i.e., comm_message) with discriminator field of some sort to indicate the type of communications (e.g. message_type). This means that some fields in the table won't be used for each type--and it means that the same message may be duplicated in several different rows in the table (if the message is sent via more than one medium).
Message "has" transports
Have a message table (comm_message) and then transports table (comm_transports) with the various different mediums of communication. A many-to-many relationship between messages and transports would mean one row for each message in the message table--but that row might have several different transports. If additional specific information is needed for a particular transport, it could be in it's own table (i.e. comm_sms, comm_email, etc..) that is linked off the many-to-many table. I.e., a "has-a" approach.
Class Table Inheritance:
Create a base message table (comm_message) and then another table for each medium with fields specific to it (inheritance). My ORM (LLBLGen) would facilitate this approach by using shared PKs for the different tables. In this approach there would be a row in the base table (comm_message), plus rows in each of the related tables for each transport (comm_email, comm_sms, etc.) but there would be no many-to-many relationship. Rather the records across different tables would share the same PK (1-1). This would be more of an "is-a" approach.
Context: This is a medium sized application (around 100 tables) that I'll be maintaining for many years--so I'd like to get this "right". I'll often need to present all the communications info together in the UI in a grid, reports, etc..
Which should I use? Why?