Search code examples
mysqldatabase-designmany-to-manyrelational-database

Mapping two mapping tables together?

database design

I am trying do design a complex database and I have a few questions.

1: Is it wrong in any way to map two mapping tables together? Regardless of the reason, I am trying to find out if it's against normalization or best practices.

2: How can I improve, or avoid mapping two mapping tables together like in the attached image?

Rules:

Course - contains every course (training that we do)

Courses can be of more types, types are stored in courseType table

Courses will always have people which are of more types (stored in people table), each of these people will have a role (roles are stored in role table.

Some courses will accept only some types of roles, that's why i did the courseTypes_has_roles table.

people_has_role table is mapped so that I can see which people can be which role for which course type.

course_has_people_has_roles is mapping which people are assigned with which role for every course. It's very important to know that some people can have more roles for a type of course, but they will have only one of them for each separate course.

Things i'm trying to avoid: I don't want to specify what role has each person in the course table because there will be people who are assigned roles but they don't belong to any course yet.

Solution

  • There is nothing wrong with your design. Course_has_people_has_roles seem to mean "person P is assigned role R for course C" and People_has_role seems to mean "person P may be assigned role R". These are two relationships which cannot be derived from each other. But every P-R subrow in the former must appear in the latter, so there is a foreign key from the former to the latter on {P,R}.

    PS Your use of "map" is odd. (Although "relationship" is misused similarly by many conventional methods & tools.) Those arrows are foreign keys. A foreign key holds when subrow values for a column set must appear as subrowvalues for another column set. In ER-style diagrams they typically are foreign keys to entity ids in entity tables or sets of entity ids in other relationship/association tables. Application relationships/associations (n-way "mappings") are represented by tables. If that condition holds, just say so by putting in the arrow. If you want to record an application relationship, make a relationship/association/table. If you can express some in terms of others, then you don't need them all.

    PPS "Has" doesn't mean anything. It's says some things are related/associated. Of course they're related, any number of things are related in any number of ways. Which way? Using "Has" in a table name is like using "Flag" in the name of a boolean variable or "Count" in the name of an integer variable. Compare to boolean "Poisons" or integral "ThousandsOfVoltsAC". A table holds the rows that make some parameterized statement--predicate--true. It represents an application relationship/association. Use its predicate to name the particular (among endless) relationship/association it represents.