Association Tables

Question

I can use an association (or ‘linking’) table to model a many-to-many relationship, eg Student <- Enrolment ->Course, where an enrolment describes an essential relationship between students and courses.

I can also have Course -> Department and Course -> Language, where language and department are both attributes of a course, but there’s no special relationship between them.

These two scenarios look similar, but I wouldn’t call Course an association table... is there another name for it? Is there a logical way to distinguish between the two cases?

Answer 1

The technical name for your association (or 'linking') table is an associative entity - there are ~ 15 alternative names listed on that page. I explore the logic behind these here - in particular that the PRIMARY KEY for a linking table should be the combination of both FOREIGN KEYs in the linking table.

Without seeing your DDL, it strikes me that your Course -> Department and Course -> Language tables are examples of your common or garden Parent -> Child relationship. These are explained clearly and simply here.

The parent table field must either be the PRIMARY KEY of the table or at the very least UNIQUE (and indexed in that case, PKs normally being indexed automatically). The child table field obviously doesn't have to be UNIQUE but should be indexed because it would normally be the JOINing field in any SQL queries. Thanks to the delights of NULL value logic, a UNIQUE parent field can be NULL (see fiddle here), however I would strongly recommend adding a NOT NULL constraint to that parent field!

I would imagine that your Course -> Department scenario would look something like this (see the fiddle here - example is PostgreSQL but it works for every server on dbfiddle.uk):

CREATE TABLE department
(
  dept_id INTEGER NOT NULL PRIMARY KEY,
  dept_name VARCHAR (25) NOT NULL,
  dept_head INTEGER,  -- this could relate to an id in a personnel table, allowed to be NULL if position is vacant
  dept_contact INTEGER, -- id of secretary to the head of department?
  dept_address VARCHAR (100),
  --
  -- other fields
  --
);

and

CREATE TABLE course
(
  course_id INTEGER NOT NULL PRIMARY KEY,
  course_code VARCHAR (10) NOT NULL,
  course_name VARCHAR (50) NOT NULL,
  course_dept INTEGER NOT NULL,
  --
  --  other fields
  --
  CONSTRAINT course_dept_fk FOREIGN KEY (course_dept) REFERENCES department (dept_id)
);

The important part (for this discussion) of these table declarations is:

CONSTRAINT course_dept_fk FOREIGN KEY (course_dept) REFERENCES department (dept_id)

So, the department table in this case is the parent and the child table is course which I imagine is the thrust of your question. The CONSTRAINT means that for any course_dept entry in the course table, there must be a corresponding entry in the department table! If you find this explanation unsatisfactory, please let me know (why) and I'll try to clarify further.

p.s. welcome to the forum!