0

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.

4
  • So you have a table containing two columns: permission holder and subject. Reciprocal permissions means two rows.
    – Ant P
    Jan 23, 2020 at 18:21
  • I don't have that table yet, but yes. One option is to create the table you mention, but that would mean to have two rows. I am not sure that is better than having only one row (or registry) and specify the "direction" of the permission in two different specific fields. Jan 23, 2020 at 18:23
  • @RodrigoCaicedo Two rows certainly seems the way to do it. Having an extra "direction" field will force your app to add extra logic that wouldn't be needed otherwise. Database storage is cheap, codelines aren't.
    – T. Sar
    Jan 23, 2020 at 19:04
  • @T.Sar-ReinstateMonica Thanks for your comment, makes complete sense. Please put it as an aswer to my question to accept it. Jan 23, 2020 at 19:07

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.