Postgresql – A question about foreign keys and many to many relationships

database-designforeign keymany-to-manypolymorphic-associationspostgresql

My question is mostly conceptual in nature. I have a database schema that involves a many-to-many relationship between a School table and Student table. This seems sensible to me, given that a school can have many students, and a student can attend many schools. I'm hung up on the problem of whether to include the school as a foreign key in the tables that are directly beneath the Student table in the information hierarchy (Behavior, Attendance, Grade).

In short, the many-to-many relationship between School and Student allows for the possibility that a student referenced in the Attendance table would have multiple schools listed in the linked Student table. This could result in unwanted ambiguity when going over attendance records. (i.e. Did they miss class in school A or school B?) My solution is obvious: include a School foreign key for any tables beneath Student to make their relationship to a given school explicit.

The problem: this seems inelegant. Aesthete that I am, I would like the information to flow through tables, instead of jumping tables. A convenient solution I've encountered would be to rely on a polymorphic association, wherein the primary key passed to the tables beneath Student (Behavior, Attendance, Grade) provides unique values for both the student and school. Unfortunately, it appears that SQL won't tolerate that approach.

Is there a solution that allows me to enjoy the apparently sensible data flow offered by polymorphic association (illustrated below) without breaking the rules of SQL, or am I trying to have my cake and eat it to?

Best Answer

Given that a school can have many students, and a student can attend many schools

.. you should have a many-to-many relationship between school and student - implemented as separate table. (school_id is misplaced in the table student.) I'll simply name it school_student:

CREATE TABLE school_student (
   school_student_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY  
 , school_id  int NOT NULL REFERENCES school
 , student_id int NOT NULL REFERENCES student
 , ...
);

And then use school_student_id in tables behavior and attendance.
Alternatively, you can use a multicolumn PK on (school_id, student_id) and carry over (school_id, student_id) as FK - effectively implementing what you had in mind - with standard SQL. Like:

CREATE TABLE school_student (
 , school_id  int NOT NULL REFERENCES school
 , student_id int NOT NULL REFERENCES student
 , ...
 , PRIMARY KEY (school_id, student_id)
);

You don't need that for grade, since that references a course bound to a school already. But you might implement something to enforce integrity so that students can only attend courses at schools they are enrolled at. If you use (school_id, student_id) as FK, that can be enforced automatically - using a single copy of school_id for both FKs in table grade.

Enforcing constraints "two tables away"

Related Solutions

Design question about student assessment data

The first thing you want to do to keep your thinking straight is to choose some clear terminology. You are using the word "assessment" in two different contexts: (i) a Test and (ii) the results for a student taking the test. I understand that "test" may not be the best term, since you might be assessing based on lots of different kinds of work. The important thing is that you need to clearly differentiate between a measuring instrument and the results of an individual student against that measuring instrument.

Keeping these concepts separate will help you to keep your thinking straight when designing your database.

I'd recommend that all of your RESULTS be kept in a single table. That will make things like report cards easier to handle. Whether the result is a percentage, a letter grade or a pass/fail, you probably want to have a common representation (percentage is a good lowest common denominator) and a weight to help you aggregate marks across assessments.

Composite primary key from multiple tables / multiple foreign keys

Short version:

CREATE TABLE Relation
( stu_id INT NOT NULL REFERENCES Student,
  par_id INT NOT NULL REFERENCES Parent,
  PRIMARY KEY (stu_id, par_id)
) ;

Long version:

Why use short forms for names, like stu_id and par_id? Why not student_id? Saving typing 3-4 characters? How will you differentiate between parent_id and parameter_id? Or school_id and schoolmaster_id?
The name "Relation" is not very descriptive for a relationship. (Note also that in relational model terminology, "relation" has a meaning very close to "table".) I couldn't come with a good name though, so we could use "Guardian" or "Student_Parent" (this combination is often used in intersection tables)
The short version above is just an example. While it is working, it uses a lot of shortcuts, like the inline references. It's far better in my opinion, to name all constraints and declare all (primary, unique, foreign key and check) constraints after the column declarations, like in the long version below.
It's also good to choose some naming conventions anduse them consistently in all the tables, e.g. Tablename_PK for the primary keys, ReferencedTable_referencingTable_FK for the foreign keys, Something_UQ for the unique constraints, etc.

CREATE TABLE Guardian
(                                        -- columns
  student_id INT NOT NULL, 
  parent_id INT NOT NULL,
                                         -- constraints
  CONSTRAINT Guardian_PK                 -- the name of the PK constraint
    PRIMARY KEY (student_id, parent_id),

  CONSTRAINT Student_Guardian_FK         -- the name of the FK constraint
    FOREIGN KEY (student_id) 
    REFERENCES Student (student_id)
      ON UPDATE CASCADE                  -- the actions of the FK
      ON DELETE RESTRICT,
  CONSTRAINT Parent_Guardian_FK          -- second FK
    FOREIGN KEY (parent_id) 
    REFERENCES Parent (parent_id)
      ON UPDATE CASCADE
      ON DELETE RESTRICT
) ;

Best Answer

Related Solutions

Design question about student assessment data

Composite primary key from multiple tables / multiple foreign keys

Related Question