sql - recursive associations many to many and primary keys and foreign keys

Question

Hi i have ER scheme with an recursive associations many to many with primary keys for example:

        ______(0,N)_________
 ID    |     base            
  course                  preparatory
       |___advanced_________/
             (1,N)

where course is entity and preparatory is association nad course have ID primary key.

My question is:
course have ID primary key but, preparatory association?

I suppose that preparatory association have base and advanced primary keys and ID_course foreign key:

course       primary key:ID
preparatory  primary key:base,advanced,ID_course  foreign key:ID_course

is it right?

question from:https://stackoverflow.com/questions/66055577/recursive-associations-many-to-many-and-primary-keys-and-foreign-keys

Answer 1

In your model, I assume that preparatory represents all the courses that are required to be completed because you can start the principal course.

In that case you are correct, you will need a separate table to manage the multiple IDs, I would suggest something like this:

CREATE TABLE Course(
   ID INT IDENTITY(1,1) NOT NULL,
   CONSTRAINT Course_PK PRIMARY KEY (ID)
);

CREATE TABLE Preparatory(
   ID INT IDENTITY(1,1) NOT NULL,
   Advanced_CourseID INT NOT NULL,
   Required_CourseID INT NOT NULL,
   CONSTRAINT Preparatory_PK PRIMARY KEY (ID),
   CONSTRAINT Preparatory_Advanced_Course_ID_FK
              FOREIGN KEY (Advanced_CourseID)
              REFERENCES Course (ID),
   CONSTRAINT Preparatory_Required_Course_ID_FK
              FOREIGN KEY (Required_CourseID)
              REFERENCES Course (ID)
);

The expectation is that Advanced_CourseID represents the princial singular reference and there will be unique Required_CourseID for each Advanced_CourseID.

To further describe this I would suggest declaring a unique index on this relationship to enforce the desired structural integrity:

CREATE UNIQUE INDEX CONCURRENTLY Preparatory_Advanced_Required_Course_ID_UX
ON Preparatory(Advanced_CourseID,Advanced_CourseID);

ALTER TABLE Preparatory
ADD CONSTRAINT Unique_Preparatory_Advanced_Required_Course_ID
UNIQUE USING INDEX Preparatory_Advanced_Required_Course_ID_UX;