-1

I have the following tables

Company: Id,Name

Person: Id,Name

A company can have one or more directors. A director can be either another company or a person. To link them I have a table Director:

Director: Id,CompanyId,DirectorCompanyId,PersonId

where if a company is a director DirectorCompanyId has a value and PersonId is null or if a Person is a director PersonId has a value and DirectorCompanyId is null

But I feel like this is not a correct design.

codejunkie
  • 83
  • 9
  • Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Feb 02 '19 at 23:22
  • What is your question? What parts can you do that you consider correct? See [ask] & hits 'googling stackexchange homework'. PS This is an anti-pattern for subtyping/inheritance. – philipxy Feb 02 '19 at 23:22

2 Answers2

0

You're right, it's not a correct design. To decompose a M:M relationship into two 1:M relationships you need a third table: 

CompanyPerson

--these columns are vital to decompose the many:many relationship
--the PK of this table should be a compound of these two columns
--so that the same person cannot twice work for the same company
--with different roles etc
PersonID -> FK to Person.ID
CompanyID -> FK to Company.ID

--plus other properties like:
RoleID -> FK to Role table --if roles are a defined set of options
StartDate -> --when the person began this employment
ManagerPersonId -> --the person's mananger.. etc

PersonID + CompanyID is the composite primary key for this table

It maps people to companies and the role they have at each. Can also have other info like start date, manager at that Company etc.. (might also need to make start date part of primary key if a person will ever leave and come back to the same co, and you wanted to recycle the PersonID)

Note: You could call this table Employee, because that's effectively what the people inside it are, but I tend to find it more helpful that these middle-man tables that create associations between two other tables are better off called Table1Table2 because you can more clearly see/understand the relationship/purpose of the table than if it's called something more abstract like Employee

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
0

The following design seems to be corresponding to demands

enter image description here

Another option is to use inheritance: 

Director <-- CompanyDirector
         <-- PersonDirector
serge
  • 992
  • 5
  • 8
  • Thanks for your reply. What about when there are more entities like companies. need to create n number of tables for each new entity. That will make it impossible to query – codejunkie Dec 03 '18 at 18:50
  • Either you join with a single table corresponding to particular type or join with a view (virtual table) which includes links of all types. – serge Dec 03 '18 at 22:23
  • thanks for you reply. lets say you have two more entities (foundation and association). Both can be set as company directors. So now directors of a company can be a company, person, foundation or association. Can you update the diagram to show what you mean please? – codejunkie Jan 31 '19 at 04:39
  • @codejunkie adding new entities requires adding a couple of tables `things` and `thing_directorship` as it implemented for `companies`-`company_directorship` and `persons`-`person_directorship` – serge Jan 31 '19 at 15:00