I'm building a Laravel Sanctum API project for my portfolio. The project is a system where users and trainers exist, and it basically allows trainers to create workout plans for users. Without going too deep into the workout side of things, here's my situation:
I was planning to create a single users table to authenticate everyone with a bearer token, and create an intermediate table containing the FK of clients and the FK of trainers, along with additional data like start date, etc. The thing is, when I start defining the relationship methods between tables, I realize I can't use a belongsTo (for the client-trainer relationship) because I can't tell it which table to look in for the client — the clients table has no fields referencing any trainer ID. I think I don't have issues with the hasMany (trainer to clients), but I still need to store related data like the start date, which is why I think the intermediate table makes sense.
My question is (sorry for the long explanation): does what I'm planning make sense and could it be considered good practice (or at least not bad practice)? Or is it a mess?
pd: i've used AI to translate this, because I don't really domain English yet. Sorry :(
Yes ,keep one users table & a self-referencing many-to-many pivot (e.g. trainer_client with trainer_id, client_id, start_date). In your User model:
php public function clients() { return $this->belongsToMany(self::class, 'trainer_client', 'trainer_id', 'client_id') ->withPivot('start_date'); }
public function trainers() { return $this->belongsToMany(self::class, 'trainer_client', 'client_id', 'trainer_id') ->withPivot('start_date'); }
This is perfectly fine.
Even if the user can only have one trainer at time? There is no problem with it?
Thanks for your help :D
Yeah, your approach actually makes sense, using a pivot/intermediate table for trainer-client relationships is pretty standard, especially when you need extra fields like start date. Definitely not bad practice.
For the initiation date, should I just use the default created_at timestamp that Laravel adds to the table?
Thanks :)
@mikelmedina Yes, my advice is to use a single table for users. If some users can be trainers, then you could either have some sort of TrainerProfile model that is attached to those users, or just inherently determine if a user is a trainer via the existence of related models, e.g. if they have defined any workouts.
For the associations between users and trainers, I’d create a separate pivot table with two foreign keys, both pointing to a user record. One pointing to the trainer, and one pointing to the user that’s appointed that trainer. If a user can only appoint one trainer at a time, then you can make that foreign key unique.
If when a user selects a trainer, that appointment is temporary (i.e, has a start date and end date) then you can add those as columns on the pivot table. By doing this, you’d also get a historic record of what trainers a user has worked with, and vice versa.
For the initiation date, should I just use the default created_at timestamp that Laravel adds to the table?
For the end_date I'd create a new column , but for de initiation date? is it necessary if I already have de created_at?
I’d personally add a separate timestamp column for recording “start” and “end” dates, just so users can set up training to start at a future date rather than the exact second a record just happens to be created in your database.
Please or to participate in this conversation.