1. Model more complex relationships
In the last few exercises, you made your first steps in modeling and implementing 1:N-relationships. Now it's time to look at more complex relationships between tables.
2. The current database model
So you've added a 1:N-relationship between professors and universities. Such relationships have to be implemented with one foreign key in the table that has at most one foreign entity associated. In this case, that's the "professors" table, as professors cannot have more than one university associated. Now, what about affiliations? We know that a professor can have more than one affiliation with organizations, for instance, as a chairman of a bank and as a president of a golf club. On the other hand, organizations can also have more than one professor connected to them. Let's look at the entity-relationship diagram that models this.
3. The final database model
There are a couple of things that are new. First of all, a new relationship between organizations and professors was added. This is an N:M relationship, not an 1:N relationship as with professors and universities. This depicts the fact that a professor can be affiliated with more than one organization and vice versa. Also, it has an own attribute, the function. Remember that each affiliation comes with a function, for instance, "chairman". The second thing you'll notice is that the affiliations entity type disappeared altogether. For clarity, I still included it in the diagram, but it's no longer needed. However, you'll still have four tables: Three for the entities "professors", "universities" and "organizations", and one for the N:M-relationship between "professors" and "organizations".
4. How to implement N:M-relationships
Such a relationship is implemented with an ordinary database table that contains two foreign keys that point to both connected entities. In this case, that's a foreign key pointing to the "professors.id" column, and one pointing to the "organizations.id" column. Also, additional attributes, in this case "function", need to be included. If you were to create that relationship table from scratch, you would define it as shown. Note that "professor_id" is stored as "integer", as the primary key it refers to has the type "serial", which is also an integer. On the other hand, "organization_id" has "varchar(256)" as type, conforming to the primary key in the "organizations" table. One last thing: Notice that no primary key is defined here because a professor can theoretically have multiple functions in one organization. One could define the combination of all three attributes as the primary key in order to have some form of unique constraint in that table, but that would be a bit over the top.
5. Time to implement this!
Since you already have a pre-populated affiliations table, things are not going to be so straightforward. You'll need to link and migrate the data to a new table to implement this relationship. This will be the goal of the following exercises.