Database structure question - how would you design this?

[thethinklab.au]

@NigelG - I’ve had a look at your suggestion:

@NigelG

Store it in text fields as JSON name/value pairs.

{
“Skills Type”: “Rugby Union”,
“fr_loosehead_skill”: “yes”,
“games_played”: 10
}

That way you can have pretty much anything you like in there. Might initially be a bit more complex, but I think that is what I would be looking at. But probably not your primary concern right now.

I was struggling to work out how I store as JSON, and then display - so I tried this - I’d appreciate your thoughts on whether it’s crazy, and if so any advice/links on where I can follow how to implement the JSON solution?

So I’ve made a table:

Note: OS Field Type is an option set I created with all the different field types available

In my workflow, I ask the user about the following:

When I save, I do the following for each ‘skill’ (so up to 9 new things, although unlikely someone would possess all)

Thanks !

[thethinklab.au]

@NigelG I’m trying to display this data in a repeating group but am having a bit of trouble - I’ve taken your advice for the most part.

I have the ‘club’ id in the URL as a parameter. In plain English, I want to display people that play for the club at present.

Whether that means I display Memberships, Sport Profiles or Persons - I’m not 100% sure. It seems like it’s Sport Profiles, so this is what I’ve attempted:

Sport Profiles where role = player (easy)

…and there is a membership on the profile that
…has a club that = the club id in my parameter,

And now where I’m stuck is… - that membership has a status of approved, and does not have an end_date (stuck)’

I tried this, but I just want to end the condition where it is - it won’t let me

Below are the data types for clarity:

Person

Sport Profile

Membership

Group

[NigelG]

Your Group needs a list of Memberships.

And your Membership needs a Person

Now you can just do Club’s List of Memberships : filtered (for current and approved) each items Person : Unique elements

Dropdown here is Club to save time.

As soon as you start having Advanced searches across multiple tables it is trying to tell you something.

[NigelG]

Going the other way…

Person has a list of Memberships.

(remember, Membership is a many-to-many on Group and Person so both ends have a list and membership is for ONE person and ONE Group)

So you can do the same thing the other way.

List me out a Person’s Coaching Roles

And by gaving a list of Sport Profiles on Membership you can then list of what Clubs someone plays for and then list out their Positions.

So I start to feel comfortable that the data will work for me as is.

[thethinklab.au]

Ok I’ve progressed but still confused - I think it comes down to differences in naming mainly…

• I have a Person
• a Person has 1 or more Sport Profiles (List of Sport Profiles)
• each Sport Profile may have 1 or more Memberships (List of Memberships)
• A Membership is the relationship between a Sport Profile and a Group

Your picture was:

But now you’ve introduced Sport Profile in your last reply so I’m lost.

I’m confident after moving forward with your last reply that my Repeating Group needs to show a list of Sport Profiles (what you have called Memberships in your diagram).

This will allow me to access:

• Player information specific to a sport and club (e.g. positions), by going Sport Profile’s preferred_position.
• Person information, by going Sport Profile’s Persons’ first_name

How do I retrieve a list of Sport Profiles, where Group = the ID in my URL, and role=player, status=approved and so on…?

This is what I’ve tried, but my RG loads blank:

Here’s the data:

[thethinklab.au]

Got it working - hooray. Was an issue with how I’d entered data.

[NigelG]

Hurrah.

Yes, so you have two many-many relationships between person and group.

membership is the main one - you were a player at Club A between 1/1/2000 and 1/12/2020 etc

sport profile / membership profile (sorry I may have mixed my table names) is effectively another many-to-many between the person and the group but is also a one to many from membership.

you were a player at Club A between 1/1/2000 and 1/12/2020 and you played hooker and prop.