Database structure question - how would you design this?

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

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 !

1 Like

@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)
Screen Shot 2023-03-09 at 4.49.48 pm

…and there is a membership on the profile that
…has a club that = the club id in my parameter,
Screen Shot 2023-03-09 at 4.57.17 pm

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

Screen Shot 2023-03-09 at 5.06.23 pm
Screen Shot 2023-03-09 at 5.06.31 pm
Screen Shot 2023-03-09 at 5.06.31 pm

Below are the data types for clarity:

Person

Sport Profile

Membership
Screen Shot 2023-03-09 at 4.36.05 pm

Group

Your Group needs a list of Memberships.

image

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.

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)

image

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.

image

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

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:



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

1 Like

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.