Mysql – Designing a database structure for a permissions system

authorizationdatabase-designMySQLpermissions

Scenario

I would now like to implement a permission system so that a user is able to restrict access on his contact properties to his connections, e.g.:

  • A wants to allow B to access his name, birthday and work mail;

  • all other properties are private to B; and

  • A wants to allow C to access his name, phone number and private mail, etc.

Current considerations

I have a database scheme as shown in the picture below for an app that manages contact information for users:

enter image description here

The red table people contains basic information of the user, the yellow tables are arrays so that every user can have multiple email addresses, phone numbers etc. You may expose your contact data to other people by connecting to them as managed by the connection table.

The question

So my question now is, how can I design an efficient permission system that achieves the described goal?

Best Answer

"... the yellow tables are arrays so that every user can have multiple ..." -- Good.

Restricted access should be in the application layer. No user should be able to write arbitrary SQL.

Dates should not be separated -- it will make inefficient to filter on a range of dates. Anyway, where do you need "multiple dates"?

That's a lot of sensitive data; rethink storing all of it.

Related Question