Table relationship confirmation

[emtau]

Greetings Filemaker experts,

I'm designing some sort of athletic sport management system. As you can see it contains 3 sections; User Profiles, Development and Events.

So it goes like this:

A user registered as an Athlete or an Official (or both) and Sport(s) the he or she plays. These athletes/officials will have/had trainings, academic qualifications, and medical check ups (especially before a sport event) etc... from time to time. The Events are divided into three categories; Local, Regional and International Sport Events. Management team will be able to identify who's eligible to attend such events based on their qualifications, trainings and athletic condition.

I just want to confirm if the relationship I drew in the attached picture is correct or not before I can move on.

Thank you so much.

 

[comment]

It's hard to form an opinion based on so little information. However, one thing jumps out immediately: your structure would allow a user to participate in at most one local competition, one regional competition and one international competition.

I would expect to see a join table of Attendance between Users and Events, so that the relationship can be many-to-many (one user attends many events, one event is attended by many users).

I also wonder if you really need 3 separate tables for the events. I would think a single table, with a field for the event's type, should be quite sufficient.

I am not sure what kind of information is supposed to go into the tables under the Development label, so I can't say anything about that.

 

[emtau]

Thank you for your swift response;

However, one thing jumps out immediately: your structure would allow a user to participate in at most one local competition, one regional competition and one international competition.

I would expect to see a join table of Attendance between Users and Events, so that the relationship can be many-to-many (one user attends many events, one event is attended by many users).

Thank you, any input is much appreciated. Since I thought that 1 user cannot attend Local or Regional or International Event at the same time... I thought that a many to many relationship is not necessary. But since you pointed out that the 3 Events (Local, Regional, National) don't necessarily need to be in separate tables, since they can be fields in one table called "Events" table... I didn't think about this, then a join table would be implemented as "Attendance" table. OKAY I will consider your suggestion... but can you please give me an example of a scenario where 1 user can attend may events? or what exactly do you mean by this?

As for the Development Section; its just to keep track of Academic progress, Trainings and Medical status of an Athlete.

Thank you again and I hope to hear from you or anyone soon.

[comment]

11 hours ago, emtau said:

I thought that 1 user cannot attend Local or Regional or International Event at the same time

That's true - but once they have attended an event, I think you would want to keep a record of that. Then, when they attend the next event, you need a place to record this - without erasing the existing data.

 

11 hours ago, emtau said:

can you please give me an example of a scenario where 1 user can attend may events?

I believe I just did. If you like, consider a competition that takes place once every year. A user attending in years 2016, 2017 and 2019 would have 3 related records in the join table.

 

[emtau]

18 hours ago, comment said:

I believe I just did. If you like, consider a competition that takes place once every year. A user attending in years 2016, 2017 and 2019 would have 3 related records in the join table.

So its like Actors in Movies? Ok let me re-think the whole thing and then draw a whole new relationship considering your suggestions... Thank you.

[comment]

1 hour ago, emtau said:

So its like Actors in Movies?

Yes, very much so.

 

[emtau]

How about now?

So User_Profile table contains a field called Registered_As, in which a user will have the option to choose Athlete or Official or Both (from a value list popup or something), then the rest of the fields are just more info..

This User then can participate in a Sport_Event, required that he participate as an Athlete, Official or both.. with a team or not, what sport in which category etc... (I'm having some doubts on where to put the "Ranking" field), but I'm placing it in the Participants table.

The Sport_Events table contains a field called Event_Level which could also be a popup from value list of Local, Regional or International Level type event.. etc. Then Event_Name field like Oceania, Olympic etc.. and the rest.

Will this work?

[comment]

That sounds about right.

1 hour ago, emtau said:

I'm having some doubts on where to put the "Ranking" field

For any field you're not sure about, ask yourself this: does it describe some quality of the user, regardless of their participation in any event? If so, it belongs in the Users table. Or does it describe the specific association of this user with this event? If so, it belongs in the join table.

1 hour ago, emtau said:

a user will have the option to choose Athlete or Official or Both

You may consider creating two separate join records in case of "both". This will allow you to create a report that lists an event's athletes and officials separately.