I have several tables in my application that can be associated with 0-many contacts in a contacts table. For example, each task in the Tasks table can be associated with several contacts/individuals (Manager, Scheduler, Planner, etc.); the same is also true with the Projects table, the Contracts table, etc. A contact can serve in several “roles” --- e.g., John Doe could be the manager of a project, the “approver” of a contract, etc.

I created the following table structure for handling contacts to avoid the need for multiple “child” contacts tables (i.e., “TaskContacts”, “PurchaseOrderContacts”, “ProjectContacts”, ContractContacts, etc.):

Roles: PK=RoleID (auto-generated)…..(this table defines contact roles “Manager”, “Planner”, etc.)
Contacts: PK=ContactID (auto-generated)…. (this table defines contacts/individuals “John Doe”, etc.)
ContactGroups: =ContactGroupID (auto-generated)…(this table contains just one field, the PK (I’ll come back to this later)
ContactRoleGroup: PK= ContactGroupID, ContactID this table is an intersection table for the previous 3 tables.. contains RoleID, ContactID, and ContactGroupID)

Each of the tables that are associated with multiple contacts (i.e., Tasks, Projects, etc.) contains the field ContactGroupID, which links it to a group of contacts.

This structure seems to work, but I’m wondering if it is “sound”. The sole purpose of the ContactGroups table is merely to group all contacts in the ContactRoleGroup table.

Here is an example with data:

Roles --- PK=1, “Manager”; PK=2, “Scheduler”; PK=3, “Planner”
Contacts --- PK=1, “John Doe”
ContactGroups --- PK=1
ContactRoleGroups --- this is the intersection of the previous 3 tables (ContactID, ContactGroupID, RoleID) --- Record1=1,1,1 … Record2=1,1,2… Record1=1,1,3

Is this structure sound? It does eliminate the need for numerous “child” contact tables (e.g.”TaskContacts”, “ProjectContacts”, etc). However, I’m concerned that the ContactGroups table only has one field (i.e., the auto-generated PK)

Thanks!

Meh.
Just create the multiple “child” contacts tables. The sql coding will be easier, the maintenance will be easier, the performance will be better, and your schema will be clearer.