Is this table structure valid/sound?

[BARJRD]

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? I've added data and it DOES work--- and 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!

 

[Pat Hartman]

You don't need ContactGroups. If contacts have fixed roles - i.e. John can be a Manager or a Planner but not a Scheduler. Ted can only be a Scheduler. Sarah can only be a manager. - then you need a contact Roles table which contains an autonumber PK, ContactID, and RoleID. You would use the ContactRoleID in the TaskRoles table to link a person with his role to a task. If any person can perform any task then you don't need the ContactRoleTable. You would link RoleID and ContactID with TaskID in the TaskRole table.