Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
Hello!
Need to design a database for the training service. I made tables: Categories, exercises, programs and one linking table programs_exercises:
categories table
id | name
1 | for arms
2 | for legs
exercises table
id | category_id | name
1 | 1→ | arms-exercise-1
2 | 1→ | arms-exercise-2
3 | 1→ | arms-exercise-3
4 | 1→ | arms-exercise-4
5 | 2→ | legs-exercise-1
6 | 2→ | legs-exercise-2
7 | 2→ | legs-exercise-3
8 | 2→ | legs-exercise-4
programs table
id | name
1 | for mass
2 | for strength
programs_exercises linking table
id | program_id | exercise_id
1 | 1→ | 1→
2 | 1→ | 2→
3 | 1→ | 5→
4 | 1→ | 1→
5 | 2→ | 1→
6 | 2→ | 3→
7 | 2→ | 7→
8 | 2→ | 8→
Now I need to make training programs for many days and part of day (morning, noon, evening). https://yadi.sk/i/HNgUbMVGk1ovPA
Can I add a date and part of day (morning, noon, evening) to the linking table? And then how to filter data by date when displaying programs? And how to insert data if the Date column is not the key to something?
programs_exercises linking table
id | program_id | exercise_id | date | part_of_day
1 | 1→ | 1→ | 2020.09.25 | morning
2 | 1→ | 2→ | 2020.09.25 | morning
3 | 1→ | 5→ | 2020.09.25 | noon
4 | 1→ | 1→ | 2020.09.25 | noon
5 | 2→ | 1→ | 2020.09.25 | morning
6 | 2→ | 3→ | 2020.09.25 | noon
7 | 2→ | 7→ | 2020.09.25 | evening
8 | 2→ | 8→ | 2020.09.25 | evening
I mean that with such a request
$programs = Program::with('exercises')->get();
we will not get the date and morning/noon/evening from the link table.
@ev-genius a note in your link table. Laravel convention recommends not using plurals and putting model names in alphabetical order so ‘programs_exercises’ would be ‘exercise_program’. This will allow you to drop the additional key names on your belongsToMany relationships and reduce some complexity,
To get the extra values off your pivot table you should just specify pivot fields like
return $this->belongsToMany('Role')->withPivot('foo', 'bar');
Oh, it works! Thank you very much!
Сan i ask one more question? Maybe it would be more correct to make a table "training diary"
diary
id | date | part_of_day
1 | 2020.09.25 | morning
2 | 2020.09.25 | morning
3 | 2020.09.25 | noon
4 | 2020.09.25 | noon
5 | 2020.09.25 | morning
6 | 2020.09.25 | noon
7 | 2020.09.25 | evening
8 | 2020.09.25 | evening
and linking table would be like this
diary_exercise_program linking table
id | diary_id | program_id | exercise_id
1 | 1→ | 1→ | 1→
2 | 1→ | 2→ | 1→
3 | 1→ | 5→ | 1→
4 | 1→ | 1→ | 1→
5 | 2→ | 1→ | 1→
6 | 2→ | 3→ | 1→
7 | 2→ | 7→ | 1→
8 | 2→ | 8→ | 1→
And one more model Diary. And many-to-many relationship between Exercise, Program and Diary.
Would it be more correct?
@ev-genius great.
your three column join table would work too so update to that if its more value.
Thank you again!
Please or to participate in this conversation.