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 been wrestling with a choice between 3 approaches to modeling these different types of data:
Store them all together in a single table (ie, comm_message) with discriminator field of some sort to indicate the type of communications (eg, 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).
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 (ie comm_sms, comm_email, etc..) that is linked off the many-to-many table. Ie, a "has-a" approach.
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 even need to present all the communications info together in the UI in a grid, reports, etc..
I'm looking for advice/ pros and cons to each approach.
Aucun commentaire:
Enregistrer un commentaire