2

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

  • Though the scenario is not exactly the same, you may find some of the points deliberated in this Q & A of help. – MDCCL Sep 4 '17 at 14:52
  • 1
    Thanks for the link - I will read through and mark mine as a duplicate if the Q&A helps me. – S1r-Lanzelot Sep 4 '17 at 14:53
  • 2
    What is the relationship between a school and a person (student?)? If a Person goes to 0 or 1 schools you don't need SchoolPerson. In general, it's difficult to discuss design without knowing the business rules, perhaps you should start formulation sentences like "A student can enroll for 1 or more Courses", "A Course has a degree and is given by a School", etc. – Lennart Sep 4 '17 at 14:55
  • @Lennart, ya I can see that - excellent point. Updated question. – S1r-Lanzelot Sep 4 '17 at 14:59
  • 2
    Art, maybe. But MDCCL's excellent post illustrates some underlying design principles for dealing with subtypes in a relational environment. Learn those principles, and it will be better than learning hundreds of tips and tricks. – Walter Mitty Sep 5 '17 at 7:49
2

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!

  • Ok I am following this, let me play with the implementation for a bit and i'll follow up with how it turns out - thanks for spending your time to help me. – S1r-Lanzelot Sep 4 '17 at 16:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.