Questions with table design

@vincent15000

1.- I will probably use a polymorphic relationship for the categories, where events and participants will have a morphToMany relationship with categories model

3.- The classification of each stage will be the sum of the times of all the stages disputed so far.

@mendizalea Ok ... well it seems quite good.

For the times table, I would rather remove the id_participant_event and add a id_participant and an id_event. because the event_participants is just a pivot table. Same remark for the penalties table.

I also notice that you don't use the Laravel naming conventions, I suggest you to rename your tables likes this.

events
stages
participants
categories
event_category
event_participant
participant_category
times
penalties

All tables in lowercase and plural, except the pivot tables in singular.

Concerning the foreign keys, I suggest you to rename them like this.

event_id
category_id
paricipant_id

Furthermore yes it's a good idea to choose a polymorphic relationship for the categories.

table : categoryables
fields : categoryable_type, categoryable_id

@vincent15000 thank you very much for your answer.

I will change the names of the tables according to the Laravel convention, as you recommend. The only thing, for the polymorphic relationship according to Laravel documentation would be in the plural: https://laravel.com/docs/10.x/eloquent-relationships#custom-polymorphic-types

I have a question, an event can have several categories and the categories share the same stages, but not all the categories will complete all the stages or even if they complete all of them in some category, some of the stages will be training, not counting the times in the general. What would be the best way to do this? I'm a bit stuck on how to organize this.

@mendizalea I think that you could add an is_training boolean field in the appropriate table.

Here you have the many to many polymorphic relationship.

https://laravel.com/docs/10.x/eloquent-relationships#many-to-many-polymorphic-relations

I have updated my previous answer with the plural for the table name.

@vincent15000 Yes, that's what I had thought, but in stages I can't do it because otherwise it would affect all the categories, I will have to create a pivot table to say in which stages is training. Do you see it correct? Or do you suggest a better way to do it?

@mendizalea Why not adding the is_training field in the times table ? So it won't affect the categories.

@vincent15000 Yes, it could be, but the results table will be fed through a webservice with an API and to mark that time as training I need to tell the system which stages are training for some specific categories and/or in which stages some categories do not participate.

@mendizalea So why not add also a category_id field to the times table ?

@vincent15000 the times table is where the start and end times of each participant will be stored in each of the stages. To make the final classification, I need to sum the times inverted in all the stages of each participant. But not everyone has to take part in all the stages.

event_id, stage_id, participant_id, start_time, end_time, time_inverted

events	
1	Test event

categories
1	Cat1
2	Cat2
3	Cat3

stages:
1	Stage 1
2	Stage 2
3	Stage 3

participants:
1	Michal
2	Elena
3	Mark

event_participant
1	1
1	2
1	3

categoryables
category_id	categoryable_type	categoryable_id
1		App\Models\Event	1
2		App\Models\Event	1
3		App\Models\Event	1
1		App\Models\Participant	1
2		App\Models\Participant	2
3		App\Models\Participant	3

Category 1 will participate in all stages (it will have 3 records in times).

Category 2 will only participate in the first 2 stages (it will have 2 records in times).

Category 3 will participate in all stages but the first one will be a training stage (it will have 3 records in times but one of them will not be taken into account when making the total sum of all stages).

Each participant can have more than one category, although in the example it only has one.

I don't know if I have been able to clarify what I need or if I have confused you even more.

Thanks for your time

@mendizalea Well ... perhaps the idea would be to add the is_training field as an additional field to the categoryables pivot table. Or perhaps to the event_participant pivot table ?

@vincent15000 sorry but I can't seen it. Because what I need is the relationship of each category that user belongs in which stages it has to participate and if any of them is training.

@vincent15000 In the end what I did is create an itineraries table where I create the itinerary to complete for each championship and whether it is training or not. Thanks for all!

@mendizalea If you have solved your problem, please close the post by assigning the best answer to the answer that best helped you.