Database Design – Parent and Child Relationship with Same Foreign Table

database-designerd

Business Rules (I realize these rules are a little abnormal):
A Person (student) can attend multiple schools. Once one or more Schools are chosen, the student can pick one or more degrees at each school. Once a degree is chosen, the Student has an option of choosing to participate in many DegreeSpecificGroups, Courses, and OptionalTrips.

I have 4 tables:

Table Person
Table School
Table Degree
Table DegreeSpecificGroup
Table Course
Table OptionalTrip

Assume that each table has a need for a unique set of properties for each Person. However, I am trying to keep this simple. My question is – where should I keep the relationships with the Person table? Do I make a relationship table for each combination – ie: PersonSchool, PersonDegree, PersonCourse, PersonOptionalTrip…?

I am having troubles convincing myself to do this because it contradicts my hierarchical thinking – IE: you cannot attend an OptionalTrip unless you are enrolled in the School and have selected a Degree. Half of me wants to keep all relationship information in the highest relationship level (SchoolPerson) – but of course that table will become bloated and unmanageable. What is the proper way to handle these multi tiered relationships?

I have looked at some ER diagrams to see some examples of other diagrams but they do not have the level of detail I am looking at
enter image description here

Best Answer

You have a Person, who is attending a School? They are studying for a Degree? A Degree will consist of 1 -> many Courses. A Course may be on many Degree programmes (e.g. Foundation Maths)?

The totally general case is that 1 Person can attend 1 or more schools studying for 1 or more Degrees in each school at any one time - is this possible? Some people may be repeating one course but have been allowed to advance, contingent on passing an exam elsewhere?

So, in order to be completely general, you want a Person table, a Degree table and a School table - with Person_Degree being your joining table between Person and Degree and Person and ultimately School. If a Person can only be doing 1 Degree in 1 School at any time, then you can simplify.

You then have a Degree_Course table which will have the joins between Degrees and Courses and a Course table.

Some Degrees can be multi-School - then you'll just have to have a further joining table. However, remember the YAGNI principle - if you don't have a current need, don't include it in your design. This can be difficult to judge - we want our systems to be flexible, but I'm with Einstein on this - make it as simple as necessary but no simpler!

Before a first take on the tables themselves - you could do worse than having a quick look here - you might get some ideas (and I might have even overlooked something! :-) )

Two things to bear in mind - one is that DDL (Data Definition Language) is quite powerful in its own right - right there in the database structure is a lot of "intelligence" - i.e. you can't have a degree without a school or a course without a school! The various other UNIQUE, PRIMARY KEY and FOREIGN KEY constraints enforce other logical rules about the system - rules you would otherwise have to spend a lot of time enforcing in your app!

Finally, a couple of words about table names.

I always use singular names for tables - they are like a class definition and/or a collection - (for me it is a singular concept - you might disagree). As an aside, you have tables in which you might want to have only 1 or even 0 records (think nuclear power plant system with a table called "catastrophic_meltdown"). In any case, choose one convention and stick to it.

Furthermore, my table_names_might_appear_long! :-) It can be greatly helpful when debugging to have meaningful table names - code spends >> 99% of its time in maintenance, so the extra typing is a (very) small price to pay for helpful error messages when things go wrong - and they will, no matter how good a programmer you are!

In PostgreSQL, I would do it like this (should translate reasonably well to other systems - check your documentation for details!) - note that I haven't dealt with semesters/trimesters here - adding a semester field to the degree and course tables might be necessary - again, this depends on your system requirements.

CREATE TABLE person
(
  person_id SERIAL,
  CONSTRAINT person_pk PRIMARY KEY (person_id), 

  person_first_name VARCHAR(25) NOT NULL,
  person_last_name  VARCHAR(50) NOT NULL

  --
  -- .. other person stuff... address, phone &c.
  -- .. could make f_name + l_name UNIQUE?
);

CREATE TABLE school
(
  school_id SERIAL,
  CONSTRAINT school_pk PRIMARY KEY (school_id),
  school_name VARCHAR(50) NOT NULL

  -- other school stuff
  -- Tel., address... &c.
  --
);

CREATE TABLE degree
(
  degree_id SERIAL,
  CONSTRAINT degree_pk PRIMARY KEY (degree_id),
  degree_name VARCHAR(50) NOT NULL,
  degree_code VARCHAR(10) NOT NULL,

  degree_school_id INTEGER, -- no "NOT NULL, since it is an FK field - see below

  CONSTRAINT degree_school_fk FOREIGN KEY (degree_school_id) REFERENCES school (school_id)

  -- other degree stuff - faculty, prerequisites (another table?)

);


CREATE TABLE course
(
  course_id SERIAL,
  CONSTRAINT course_pk PRIMARY KEY (course_id),
  course_name VARCHAR(50) NOT NULL,
  course_code VARCHAR(10) NOT NULL,

  course_school_id INTEGER,
  CONSTRAINT course_school_fk FOREIGN KEY (course_school_id) REFERENCES school (school_id)


  -- other stuff - possibly a course_prerequisites table
);

CREATE TABLE degree_course
(
  degree_course_id SERIAL,
  CONSTRAINT degree_course_pk PRIMARY KEY (degree_course_id),
  degree_course_degree_id INTEGER NOT NULL,
  degree_course_course_id INTEGER NOT NULL,

  CONSTRAINT degree_course_degree_course_uq UNIQUE (degree_course_degree_id, degree_course_course_id),  -- prevent same entry twice

  CONSTRAINT degree_course_degree_fk FOREIGN KEY (degree_course_degree_id) REFERENCES degree (degree_id),
  CONSTRAINT degree_course_course_fk FOREIGN KEY (degree_course_course_id) REFERENCES course (course_id)
);

CREATE TABLE prerequisite
(
  prerequisite_id SERIAL,
  CONSTRAINT prerequisite_pk PRIMARY KEY (prerequisite_id),
  main_course_id INTEGER,
  preq_course_id INTEGER,
  CONSTRAINT prerequisite_uq UNIQUE (main_course_id, preq_course_id)  -- prevent same entry twice

  -- other info...
  --
);

CREATE TABLE person_degree
(
  person_degree_id SERIAL,
  CONSTRAINT person_degree_pk PRIMARY KEY (person_degree_id),

  person_id INTEGER NOT NULL,
  degree_id INTEGER NOT NULL

  -- various FOREIGN KEY and UNIQUE constraints to be added here!
);

Similar principles apply to your optionaltrip and degreespecific group tables - if you're having further issues, post back!

Related Question