In an ERD, can a foreign key of a table refer to the primary key of the table on other the side of of an intermediate table without a new relation

Question

Imagine an ERD with a table of Employees (employee_id as primary key), Tasks (employee_id, project_id as composite primary key and foreign keys), Projects (project_id as primary key). Tasks is the intermediate table of Projects and Employees.

Employees is in a relationship with Tasks, Tasks is in a relationship with Employees and Projects, Projects is in a relationship with Tasks.

Now, imagine without drawing an additional relationship between Projects and Employees, I put a foreign key called e_id in Projects that refers to employee_id of Employees.

Am I allowed to do this without drawing an additional relationship, I wouldn't think so. I mean they're connected through an intermediate table, but I don't think it's legal to do that in an ERD diagram and that really brings me to my question sub-question:

In an ERD Diagram can a foreign key in a table only refer to a primary key in a different table (or same if recursive) if those two tables have a drawn relationship (with this I mean a line drawn from one table to the other without first making a stop through a intermediate table)

I really hope my question is clear

Have an amazing day!

Answer 1

Imagine an ERD with a table of Employees (employee_id as primary key), Tasks (employee_id, project_id as composite primary key and foreign keys), Projects (project_id as primary key). Tasks is the intermediate table of Projects and Employees.

• Employees is in a relationship with Tasks.
• Tasks is in a relationship with Employees and Projects
• Projects is in a relationship with Tasks.

You mean like this?

Now, imagine without drawing an additional relationship between Projects and Employees, I put a foreign key called e_id in Projects that refers to employee_id of Employees.

Am I allowed to do this without drawing an additional relationship?

Too late: you already have defined this new relationship: consider that an ER "relationship" is a FOREIGN KEY constraint, and vice-versa.

The act of adding a foreign-key from Projects.e_id to Employees_employee_id also means you're adding a new relationship between the Project and Employee entities.

...like so:

I wouldn't think so. I mean they're connected through an intermediate table, but I don't think it's legal to do that in an ERD diagram and that really brings me to my question sub-question

• "I mean they're connected through an intermediate table" - when you say "intermediate table" I assume you're referring to many-to-many linking tables, but consider...
  • ...in a many-to-many relationship in an ER diagram, the linking-table is not an entity.
  • The Tasks table is its own Entity (despite the Tasks table not yet having any data attributes/plain-ol-data-columns. Furthermore the fact it's called "Tasks" (a noun) also strongly-hints that it's its own Entity.
  • ...and nothing stops any entity from having a relationship with any other entity - unless you have some pressing domain-rules against it in some cases.

In an ERD Diagram can a foreign key in a table only refer to a primary key in a different table (or same if recursive) if those two tables have a drawn relationship (with this I mean a line drawn from one table to the other without first making a stop through a intermediate table)

_{"ERD Diagram"? I must report you to the Department of Redundancy Department!}

To repeat my earlier point: a "drawn relationship" or "line drawn" in an ER diagram represents a foreign-key constraint between those two tables. Drawing a line on a piece of paper is not a prerequisite for implementing a FOREIGN KEY constraint.

(Also, foreign-keys can also reference secondary-keys (aka UNIQUE KEY), not just PRIMARY KEY constraints, ofc).