Double way parent child relationship in Django

Question

I am building an app to register and update children information, this information is to be provided by their tutors. Every child can have multiple tutors, and a tutor can be a tutor for multiple children. This has been solved specifying a Table where I relate a student with each of their tutors by a unique registry, and characterize that relationship accordingly.

Now, we are facing the need to specify read/write permissions among some of the tutors that tutor a student. My idea is to create a new table where I relate each of the pairs formed by those tutors, and specify the read and write permissions. The problem is that those permissions are not reciprocal, a tutor can have permissions over another, while that other may not have permissions over the first one.

I don't know which is better, to have only one registry for each pair of tutors and add boolean fields for every type of possible permission, or define a parent-child type of registry where the parents has permissions over the child. This second option would mean having two registries for each pair of tutor, on where tutor 1 is parent over tutor 2 and another where tutor 2 is parent over tutor 1.

Please, help me determine which would be better and why.

Answer 1

You should implement this as two columns, and use two rows for reciprocal permissions.

Barring some exotic circumstances, this situation is better designed as a table with two columns - one for the permission holder, and one for the subject. If you need reciprocal permissions, you'll have two rows on said table. (as pointed out by @AntP).

A table having a column defining how other two columns relate to each other is unneeded burden for your software to check the direction of the relationship. This might get things more complicated down the line with more complex relationships. Even designing a screen to interact with this table will be more complex that it need to be.

On the other hand, if you just have two columns, nothing of that happens. You can use the usual design as any other part of the system, without special, magic code. You'll avoid problems of having to deal with invalid data on the "direction" column, you'll avoid the hassle of special code for that screen, and you'll avoid the headaches you'll give yourself three months down the line when you have to revisit this to fix a bug and don't remember what or why you did this in that strange way in the first place.

More so, having to pull two rows instead of one in a table with so little data isn't something that will bog down your database that much down the line.

Database storage is usually cheap

Codelines are usually expensive.

Don't try to reduce the amount of rows you'll store in your database if you'll end up with far more complex logic on your software in exchange.