Referencing a Join table

relational-theory

I have a relationship that I am trying to represent in a database. Imagine I have series of many to many relationships…

Say student to proffesor, and professor to classes… enter image description here

A student can many professors and professors can obviously have many students. A student also has many classes, each taught by a different or similar professor, and professors can teach many classes.

To relate those many to many relationships to each other, you obviously need a join table between each.

But, if you relate a student to a professor, you are automatically linking him to all the classes that he teaches. Which doesn't necessarily have to be the case. You could take one class with one professor, and have another class with another professor, or you could have two classes with one professor and another with another professor.

Obviously you could just link the student directly to the classes and then link the professors to the classes. But Imagine, for the sake of this question, that for some reason, you can only link students to classes. Imagine this is a legacy database for instance.

Would it be good form to instead of linking directly to the professor, link instead a link-table of the professor… So I would have a link table to a link table of say Prof ID 2, class ID 3… That would have a link id of lets say 2. I would then have a link table with student 3 linked to Prof->Class ID 3. So that student is now linked to professor ID 2 and class ID 3. Think of that relationship like this…

enter image description here

Is that a good way to represent that relationship, or is there a better way to do this?

Best Answer

To model the classical student - class - prof situation, you need to reverse your model. class can implement the n:m relationship between student and prof. To be precise:

[student] m -- n [class] n -- 1 [prof]

The n:m relationship between student and class would be implemented by another table. Like [participant]:

[student] 1 -- n [participant] n -- 1 [class] n -- 1 [prof]

A prof has many classes. A class has many participants. A student is participant in many classes. Student and prof are only linked by participation in classes in this model.

Generally, just because a student is linked to a professor in some way does not mean the student participates in all classes of the professor. That's an unwarranted over-interpretation. What a relationship between two entities actually means is defined by additional meta-data, usually indicated by the column names used.

Related Solutions

How to normalize Time Sheet data

You want to have something like this:

ERD

This allows you to have any number of work periods per staff member and client and gives you the details of who worked for who and how long (not to mention when - which is also very important!)

Database Design – Arbitrarily Ordering Records in a Table

If I want to move record 0 to the start, I have to reorder every record

No, there's a simpler way.

update your_table
set order = -1 
where id = 0;

If I want to insert a new record in the middle, I have to reorder every record after it

That's true, unless you use a data type that supports "between" values. Float and numeric types allow you to update a value to, say, 2.5. But varchar(n) works, too. (Think 'a', 'b', 'c'; then think 'ba', 'bb', 'bc'.)

If I want to remove a record, I have to reorder every record after it

No, there's a simpler way. Just delete the row. The remaining rows will still sort correctly.

It's easy to imagine a situations like:

Two records have the same order

A unique constraint can prevent that.

There are gaps in the order between records

Gaps have no effect on how a dbms sorts values in a column.

Some people have proposed using a decimal to store order, so that you can use "2.5" to insert a record in between the records at order 2 and 3. And while that helps a little, it's arguably even messier because you can end up with weird decimals (where do you stop? 2.75? 2.875? 2.8125?)

You don't stop until you have to. The dbms has no problem sorting values that have 2, 7, or 15 places after the decimal point.

I think your real problem is that you'd like to see values in sorted order as integers. You can do that.

create table your_table (
  id int primary key, 
  title varchar(13), 
  sort_order float
);
    
insert into your_table values
(0, 'Lorem ipsum', 2.0),
(1, 'Dolor sit', 1.5),
(2, 'Amet, consect', 0.0),
(3, 'Elit fusce', 1.0);

-- This windowing function will "transform" the floats into sorted integers.
select id, title,
       row_number() over (order by sort_order)
from your_table

Best Answer

Related Solutions

How to normalize Time Sheet data

Database Design – Arbitrarily Ordering Records in a Table

Related Question