CODEWITHSUNDEEP
mysqldatabasedatabase-design
Akshat Goel
Akshat Goel

Reputation: 786

Database modelling for 2 many-to-many realations

I have 3 entities

teacher : Stores the name of a teacher in the school. (e.g "teacher 1", "teacher 2")
access : Stores the name of all types type of access possible. (e.g "class_teacher", "sports_teacher", "head_master")
classroom: Stores the name of different classes in the school. (e.g. "classroom I", "classroom II")

The relationships are as follows:

The way I have modelled it right now is in following tables

  1. Teacher (id, username) (1 t1, 2 t2)
  2. access (id, access) (1 a1, 2 a2)
  3. classroom (id, class) (1 c1, 2 c2)
  4. teacher_access (id, teacherId, accessId) (1 1 1, 1 1 2)
  5. teacher_classroom (id, teacherId, classroomId) (1 1 1, 1 1 2)

A row in teacher_access stores the specific "access" a specific "teacher" has.
e.g. (1 1 1) shows that t1 has access a1

A row in teacher_classroom allows a specific "teacher" to access a specific "classroom" using the accesses he/she has.
e.g. (1 1 1) shows that teacher t1 has access to classroom c1

The problem here is that I don't want to give teacher t1 access a2 for classroom c2.

In my current design, if there is a row in teacher_access, the tId in that row will have the "access" specified by this row's aId for all the classrooms for which we have a row in "teacher_classroom" having the same teacherId. So, if I have 2 rows in "teacher_access" with (tId =1, aId = 1), (tId = 1, aId = 2) and 2 rows in "teacher_classroom" with (tId =1, cId =1),(tId = 1, cId = 2), "teacher"(1) has both "accesses"(1,2) for both "classrooms"(1,2). But what I want to do is provide only aId(1) to tId(1) for cId(1) and both aId (1,2) for cId (2) tId : teacherId aId : accessId cId: classroomId.

What I am trying to say is I want a "teacher" to have different "accesses" to different "classrooms". E.g. A teacher can have both "class_teacher" and "sports_teacher" access to classroom "classroom I" and only "sports_teacher" access to "classroom II". I am unable to do this using my current design because if a teacher has "access" (sports_teacher, class_teacher) per teacher_access and the same teacher has access to "classroom" (classroom I, classroom II), I cannot have the teacher as only "sports_teacher" for "classroom II".

What is a better way to model?

Upvotes: 0

Views: 55

Answers (2)

philipxy
philipxy

Reputation: 15157

You can describe what is going on via teacher, access, classroom and a table for

"teacher TID has access AID to classroom CID"

Accorinding to your edits and comments: Given a teacher or an access or a classroom, there isn't just one value for the rest of a row. Given a teacher and classroom, there could be multiple accesses. Given a teacher and access, there could be multipe rooms. Given an access and room, there could be multiple teachers. So you cannot describe a school situation as a JOIN of components. (That relationship is "all (candidate) key".)

You might be able to describe the situation in fewer numbers if you describe defaults and exceptions.

PS See this answer re defaults and exceptional additions & subtractions.

(Some guesses at solutions arising from earlier edits of your question follow. I use some new table names although some tables with new names might end up being some of your old tables. I use minimal ids.)

Maybe you only need two entity tables and a many-to-many table:

teacher "TEACHER identifies a teacher named NAME"
classroom "CLASSROOM identifies a classroom"
has_access "teacher TEACHER has access to classroom CLASSROOM"
    FK (TEACHER) references teacher
    FK (CLASSROOM) references classroom

To also record actual permitted accessings:

is_accessing "teacher TEACHER is accessing classroom CLASSROOM"
    FK (TEACHER, CLASSROOM) references teacher_access

On the other hand maybe an "access" is 1:1 with a set of classrooms and having an access means accessing those rooms is permitted. This situation can be recorded in the above database. But it can be organized less redundantly as follows.

teacher "TEACHER identifies a teacher named NAME"
classroom "CLASSROOM identifies a classroom"
access "ACCESS identifies an access"

access_classroom "access ACCESS acesses classroom CLASSROOM"
    FK (ACCESS) references access
    FK (CLASSROOM) references classroom
teacher_access "teacher TEACHER has access ACCESS"
    FK (TEACHER) references teacher
    FK (ACCESS) references access

Now

has_access "teacher TEACHER has access to classroom CLASSROOM"

is

select TEACHER, CLASSROOM
from teacher_access natural join access_classroom

To also record actual permitted accessings:

is_accessing "teacher TEACHER is accessing classroom CLASSROOM"

Then we would still like to have:

    FK (TEACHER, CLASSROOM) references has_access

But in most DBMSs this cannot be enforced declaratively.

Upvotes: 1

Ivan
Ivan

Reputation: 1

The way your model is right now a teacher either has access to a recource or not. What you need to do is set up a relation table that connects all three entities. E.g. teacher_room_access(id, teacherId, classId, accessId)(1 1 1 1, 2 1 1 2, 3 2 1 1, 4 2 2 2)

Upvotes: 0

Related Questions