[Resolved] Help with database model for relationships and possible intermediary post type

[Author]

Posts

[johnnyH-3]

I have the attached data model for a calendar and results database for Swedish ultrarunning events.

EVENTS
General info about a (usually) recurring event
- One event can have many courses (one-to-many)
- One event can have many races (one-to-many)

COURSES
All the info about a course, that an event can use (e.g. 50 miles, 50 km, 100 miles)
- One course can be used in many races (such as a 50 miles course in each year's race at an event)

RACES
A particular instance of an event, at a particular date, using one of the courses in the COURSES post type.
- One race can have many courses (this is one of the things I have a question about!!) - possible many-to-many with COURSES

RESULTS
Results for a particular race, including times, positions etc. for each runner.
- One result can belong to one race (OR an intermediary custom post type, see question below)

RUNNERS
Info about each runner that participates at a race and gets a result.
- A runner can have many results

QUESTIONS
Now what I'm thinking about is how to deal with dividing the data into COURSES and RACES.
1) I can choose a many-to-many relationship and let each race have many courses. That is, a race in this case is everything that is happening at an event on a particular date.
2) I can choose a one-to-many relationship and create one race for each course at an event on a particular date. That is, I have to create many races if there are many courses that year, rather than as in #1 where I only need one race with many courses.

Scenario 1:
In the case of #1, could I create an intermediary post type - possibly containing the date - to which the result of that particular instance (a specific course on a particular date) is related? In this case, the date has to be in both RACES and in this intermediary post type? Or not in RACES?

Scenario 2:
In the case of #2, I would relate each result to one race in RACES and that race has only one course. Easy enough.

In scenario 1, the advantage could be that it is easier to create a race and I can have a description for whatever is unique to the event on that particular date.

In scenario 2, it will be more complicated to create let's say 4-5 different races for an instance of an event and those races have nothing in common but the date.

DISPLAYS
The following are the displays that I am planning that are related to COURSES and RACES.
a) List of all upcoming races, with the event name from EVENTS as the title, but with the distances from the COURSES table for each race from RACES on that date being displayed. Like at hidden link
b) Upcoming and past races below the event details of the event page. Like at hidden link
c) Detail page for a course, listing all upcoming and past races for that course. Such as hidden link
d) Results page for an event. Such as hidden link
e) Results page for a particular race instance. Such as hidden link
f) Views with top results by gender and age group for a particular course. Such as hidden link

FINAL QUESTION
Which approach to the relationships between COURSES, RACES and RESULTS is the best to accomplish this?

[Luo Yang]

Hello,

Q1) relationships between COURSES and RACES

It depends on yourself, since there are existed one-to-many relationships between:
- Events and Courses
- Events and Races

They can connect the course posts with other race posts, I don't think you need to setup other relationship between post types:
Courses and Races
It will make thinks complicated.

In my opinion, you can try these:
a) List of all upcoming races, with ....
You can setup a custom date field "race_date" to post type "Races", create a post view, query "Races" posts, with custom search form, filter by the date field "race_date":
https://toolset.com/documentation/user-guides/date-filters/

b) Upcoming and past races below the event details of the event page
In a single event post, display related "Race" posts depends on the relationship "Events and Races"
https://toolset.com/documentation/post-relationships/how-to-display-related-posts-with-toolset/#displaying-many-related-items

c) Detail page for a course, listing all upcoming and past races for that course
In the single "course" post, you can use a content template to display the related "Event" post information:
https://toolset.com/documentation/post-relationships/how-to-display-related-posts-with-toolset/#displaying-one-related-item-parent
In this content template display a post view, query "races" posts, and display the "races" post information

d) Results page for an event
Similar as b), In a single event post, you can setup a nested view:
- Parent view query the related "race" posts
- Child view query the related "result" posts of "race" post from above parent view

e) Results page for a particular race instance
Same as b), you just need to setup a post view, query the related "result" posts

f) Views with top results by gender and age group for a particular course
Since there isn't relationship between between post types "course" and "result", here needs another one-to-many relationship between post types "course" and "result".
So, in a single "course" post, you can setup post views, query the related "result" posts, filter by the fields of "result" post type:
gender and age group

Q2) RACES and RESULTS relationship:
Same as above, It depends on yourself, if
- one "Race" post can connect to only one "Result" post
- one "Result" post can connect to only one "Race" post
Then I suggest you setup one-to-one relationship between "Result" and "Race"

[johnnyH-3]

Thanks a lot!

A couple of further questions:

1) Is it better to have one-to-many relationships between EVENTS and COURSES, and between EVENTS and RACES, rather than having RACES as the child to COURSES and no relationship between EVENTS and RACES?

2) With your suggestion of not having a relationship between COURSES and RACES, is there an easy way to display all the courses at a specific instance of an event? An instance of an event could be one race containing many courses/distances, or many races each with its own course/distance. Not sure how to best regard a race, to make it easy to enter data as well as to display all courses of an event instance. That is, would I consider each course/distance at an event on a particular date as a separate race, or can one race contain many courses?

I'm not sure I understand how I can choose what course(s) that are connected to a race if there is no relationship between COURSES and RACES? That is, each race is run on a course that is related to the same event.

3) Some relationships are required, such as:
- a result must always be related to a race
- a result must always be related to an existing runner
- a race must always have a course (or many courses, depending on how a race is regarded)
- a course must always be related to an event

Do I need to set up post reference fields for these required relationships? Or are no post reference fields at all necessary in the new relationships model? I mean, I should not be able to create a post that does not include a required relationship with a field in another post type.

4) Another question, not quite related to the above issue(s). Would it be better to let the EVENTS fields "Location" (could be any text) and "County" (from list of existing counties) be taxonomies or a single-line text/drop-down select respectively?

And by the way, obviously a race must contain many results (one for each runner in the race), which means it must be a one-to-many relationship. My mistake...

[Luo Yang]

1) Is it better to have one-to-many relationships between EVENTS and COURSES, and between EVENTS and RACES, rather than having RACES as the child to COURSES and no relationship between EVENTS and RACES?

That depends on your website structure, for example, if you are going to connect EVENTS and COURSES, , you can setup post type relationship between them. You can choose one of below options:
- one-to-one
- one-to-many
- many-to-many
https://toolset.com/documentation/post-relationships/how-to-set-up-post-relationships-using-toolset/

2) With your suggestion of not having a relationship between COURSES and RACES, is there an easy way to display all the courses at a specific instance of an event ...

Same as above, you need to connect courses posts with with the event post by a post type relationship, then in a single(specific) event, you will be able to display all the related courses posts

3) Some relationships are required ...
Yes, you can connect those post type with each other, then you will be able to display the related posts in admin side, and display the related posts in front-end by the relationship.

4) Another question, not quite related to the above issue(s). Would it be better to let the EVENTS fields "Location" ...
In my opinion, I suggest you setup it as a custom taxonomy "Location", then your user will be able to select the existed terms or add new term in the location taxonomy.

For your reference.

[johnnyH-3]

Thanks. I'm still not sure I understand when to use post reference fields and when to only use a post relationship. Can/should I both use a post reference field and a post type relationship when I for example want to pick the course for a race? Or should I only use a relationship and connect the course to a race that way? In the latter case I cannot choose this as being required, though.

And if I can add another question... I want to add duration as a field in the RESULTS CPT. Should I use Number and enter the duration in seconds? Or is there a way to enter the duration as h:mm:ss? Rather than using a Single-line text field, which cannot be used for proper sorting and calculations.

And last question... For the RESULTS and RUNNERS CPT's I don't want to use the Title field, but rather using custom fields for setting the title and slug. That is, I want the slug to be based on the runner_firstname and runner_lastname fields, rather than the Title field.

I tried adding the following code to functions.php, but it doesn't work, I still just get the automatic-draft slug and title. The slug for the RUNNERS post type is runner.

add_filter( 'wp_insert_post_data' , 'set_runner_title' , '10', 3 );
 
function set_runner_title( $data , $postarr ) {
if ( 'runner' != $data['post_type'] )
return $data;
 
$post_title = $data['post_title'];
 
if ( ! $post_title ) {
 
$runner_firstname = trim( $_POST['wpcf']['runner_firstname']); 
$runner_lastname = trim( $_POST['wpcf']['runner_lastname'] );
 
$runner_title = $runner_firstname . ' ' . $runner_lastname;
$post_slug = sanitize_title_with_dashes ($runner_title,'','save');
$post_slugsan = sanitize_title($post_slug);
 
$data['post_title'] = $runner_title;
$data['post_name'] = $post_slugsan;
  }
 
return $data;
}

[Luo Yang]

The "post reference field" is similar as one-to-many relationship, but it displays in the field group meta-box when you edit the post in admin side
and multiple instance "post reference field" is similar as many-to-many relationship, same as above, it display in the field group meta-box.

For the other new questions:
1) duration as a field
As a workaround, you can create three select custom fields:
- Hour
- Minute
- Second

But for sorting and calculations, you can create another number field "duration", when user save the post, use action hook "wp_update_post" to calculate the value, and fill into field "duration", then you can use field "duration" to sorting and calculations.

2) using custom fields for setting the title and slug
The action hook "wp_insert_post_data" works when you create a new post, in most case, you can use another hook "wp_update_post" to setup the post title:
https://codex.wordpress.org/Function_Reference/wp_update_post

Please create new thread for the new questions, that will help other users to find the answers. thanks