Hello,
An training organization works with several trainers.
A trainer can work for several organizations.
A trainer won't necessarily connect to the application and it's not a good idea to force a trainer to create an account on the application. But some trainers will create an account.
So organization A and B can work with trainer T1 and will save their own trainer T1 model ? Yes, but what if the trainer wants to connect to the application and have access to allowed data from A and B ?
I specify that I want to avoid to create a user account for a trainer who doesn't need any account.
What's the best design for the database structure ?
I already have some ideas, but I'd like to have your suggestions before.
Thanks for your suggestions.
V
Always store different entities (in your case people and accounts for login) in different tables.
Your tables structure may be the following:
organizations
- id
- name
trainers
- id
- name
organization_trainer (pivot table)
- organization_id
- trainer_id
users
- email
- password
- trainer_id
@Glukinho This is my first idea.
But there is a problem ... organization A creates trainer T1 and organization B creates trainer T1.
So trainer T1 is present in the the database as 2 different models.
How is it possible to solve this problem ?
@vincent15000 you should give possibility to search for already created trainers before creating new one. Maybe raise an alert "trainer with these first name/last_name/email already created!". This is handled on user interface level. There is no database structure that can prevent somebody to add a valid row into a table. Maybe unique keys on some fields (like email) can help?
Another approach is to clean doubled rows afterwards, with some procedure of deduplication and joining two rows and associated resources into one.
In my opinion and as others has said, one table, then use roles and permissions for who can do what.
@jlrdw I'm trying to think of another structure, but I don't find better. Sure no matter where the user is saved in the database (users table or trainers table), the only difference is that the users table is the one used to authenticate users.
Imagine that trainer T1 doesn't want to have an account on the application, nobody can force him.
Is there any solution to have a user in the users table, but deactivate the possibility to log in.
Sure I could add a tag field activated, but is there a better way ?
@vincent15000 sure, just add boolean can_login column to users table and tune authentication process accordingly.
@Glukinho I'm using Fortify for the authentication ... so adding a middleware to the login route seems to be a solution.
Sure no matter where the user is saved in the database (users table or trainers table)
Of course there is a big difference. A trainer is a person, a human. A user is an account (username + password usually) used to authenticate. They may look similar, but are not the same at all.
@Glukinho But if I want to avoid organization A and B to save the same trainer T1 twice, the only solution is to use the email address of the trainer.
An account is represented by a unique human person. And a trainer is a human person and cannot be twice in the database.
So there isn't a big difference between both tables.
@vincent15000 You could probably make this simple by just adding a couple of fields to the user table:
trainer | organization
But I can see how this can become more complex. Maybe the pivot table suggested by @martinbean might be the way to go.
You could also perhaps set it up similar to a multi tenant app, each organization can do what they need to do with trainers and users.
@vincent15000 If “trainers” can possibly log in, then they’re users, so it makes sense to just use the User model and users table. You can then attach users to the organizations they need access to using an organization_user pivot table, and store any details specific to that organization–user relationship as columns in that pivot table.
users and trainers can be different things. Create your trainer model and have it belong to a user.
The user model is for logging in, and the trainer model is for trainer attributes. Several trainers can belong to one user.
When the trainer logs in with their user account, you see they have more than one trainer model, you can ask them to select organisation A or B. Store their current organisation in session. Allow them to switch organisations at any time whilst logged in.
@jlrdw @martinbean @snapey You all gave me great suggestions.
Hmmm ... the idea that an organization can do what it needs with its trainers without modifying the related user is a great idea.
And having a pivot organization to store all details specific to that organization-user relationship is also a great idea.
Ultimately this pivot table could be called collaborators, each collaborator could be a trainer or a student or an assistant, ... and have its own datas specitif to the related organization.
The idea to switch from one to another organization whilst logged in is also really great (like with Slack for example), but it would be interesting too that a trainer can for example see all his/her trainings on the same view : training A for organisation 1, training B et C for organisation 2, ... with the title, the dates, the number of students, ...
The trainers will necessarily have very little access to the application (only see their trainings) ... but if a trainer is also admin of his/her own training organization, he/her has to be able to manage his/her own organization's trainers.
Please or to participate in this conversation.