How can a database be constructed to accommodate various roles effectively?

Hanan Radwan
Hanan Radwan Member ✭

When building a database for an authentication-based application with multiple users and various roles with specific permissions and different front-end interfaces, what is the recommended approach for creating the database structure?
Is it better to create a single table for users with all their information, such as name, email, and role, while storing the rest of the data in separate tables?
Or is it more advisable to create a separate table for each role?


Best Answer

  • Ray Deck
    Ray Deck Trusted Xano Expert ✭✭✭
    Answer βœ“

    The universal answer is "it depends", but the specific answer is likely that a user table that contains a list of roles associated with the user that you then use for authenticating services later on would make sense.

    I would make a function that takes in the role/permission you are looking for, and preconditions out if the currently-authenticated user lacks that permission. That way you get control at the API endpoint level for who can do what.


  • Hello again

    I want to explain the condition I have in more detail, and I want help with the solution. I have not found a video or article that helps me specifically

    Now I have a table for all users containing the name, email, and role (admin - teacher - student).
    I have three separate tables for each role
    A table for the admin, a table for the teacher, and a table for the student

    When you log in, the registration will go to the users table, but if the role is specified as a teacher
    How can this recording be automatically added to the teacher's schedule?