I am re-designing a database, with a view to having one table with all People in it [PersonID, Firstname etc], the same with Organisations [OrganisationID, Name etc]. In this database a Person can have multiple Roles e.g. be an Employee but also a Committee Member etc. as can an Organisation e.g. a Supplier and a Consultant. There is aslo a relationship between People and Organisations. My original idea was to have the following tables.
Person[PersonID, Firstname etc]
Organisation[OrganisationID, Name etc]
Roles[RoleID, RoleName]
RoleDetails[RoleID,PersonOrOrganisationID].
My problem is it is possible for a Person and an Organisation to have the same role (e.g. Consultant), therefore the RoleDetails table won't work. The second problem is there can be a relationship between People and Organisations, so I thought a change to RoleDetails would be the answer e.g.
PersonOrgRoleDetails[PersonID,OrganisationID,RoleID]
However this will mean alot of blank fields where it is simply a Person with a role or an Organisation with a role and not both. I get the feeling a need another table but can't quite get a handle on it. Any advice greatly appreciated
