I am after a bit of advice.... I am considering the design for some tables in my database and wondered if anyone has any experience/ideas about the following structures.
I need to created a CommunicationHistory table, this can hold communication history against a Customer, a Contact, a Job, a Task, a Product etc (could be associated to other things going forwards)
I create nullable fields in the CommunicationHistory table for CustomerId, ContactId, JobId, TaskId, ProductId etc
I create a table called 'Entity' that contains the common information about all 'Entities', e.g. EntityId, EntityTypeId, CreatedDate, CreatedByUserId...
Each of the Customer/Contact/Job/Task/Product tables have an EntityId FK field.
The CommunicationHistory table then has a FK to EntityId
I create a CommunicationHistory table that stores all records and then linking tables for each entity e.g. CustomerCommunicationHistory(CommunicationHistoryId, CustomerId) , ContactCommunicationHistory(CommunicationHistoryId, ContactId), JobCommunicationHistory(CommunicationHistoryId, JobId) etc...
What do you think? Are there any other options?
Thanks in advance for your help and suggestions.