I'm building a system where there are employees and companies. An employee can belong to multiple companies.
The company table:
id
name
address
industry
The employee table:
id
name
email
designation
The company_employee table:
id
employee_id (foreign key referencing Employee.id)
company_id (foreign key referencing Company.id)
is_admin
Using TypeORM I found out that the company_employee table can be implicitly defined (as the junction table for the many-to-many relationship between employee and company).
Is it better to explicitly define the table or to do it implicitly? Is there any difference? If so, what are the pros and cons?
I tend to explicitly define these relationships because the relationship between two entities is very often an entity itself. For example, consider your relationship table:
id
employee_id (foreign key referencing Employee.id)
company_id (foreign key referencing Company.id)
is_admin
The first three fields are just internal system information, but is_admin looks like business data. In the absence of a company_employee entity (which could instead be named something like employment, etc.) where would that data live?
The relationship between a company and an employee is itself an entity. It can include information about the employment contract, start date, position, etc.