I have a scenario I’m not sure quite how to cater for in the database:
A person may join and leave a club multiple times in their life, in multiple roles. For example, they have been with
– Club A 2005-2008 as a player,
– Club B 2009-2012 as a player,
– Club A 2013 as a player, and then
– Club A 2014-2018 as a coach.
For the individual’s playing history, I’d want to be able to display their total points scored, wins/losses etc, as well as their individual record at each club (Club A and Club B)
For players, I currently have a Player table, which has fields for different sporting profiles, as they may play multiple sports in their life. e.g. Field: Player Profile (Rugby Union) - Field Type: Player Profile (Rugby Union).
For coaches, I currently have a Coach table, which again has fields for different sporting profiles (it’s not impossible that someone might coach across multiple sports in their life).
I’m really stuck/lost on the complexity of this and the best way forward in terms of being able to have the data at a granular club level, but also at a rolled up level. I’m thinking the best way forward is to have a junction or associative table like ‘Player Club Relationship’ table which holds the Club and Player, along with other info such as Date Joined, Date Left and data/stat fields (total points, wins etc etc). This means I could have an entry for each time the player was at the club.
Appreciate any suggestions, or let me know if you need more info to work though this… Thanks!
The issue with having Player and Coach on club, is that a club can have many Players and Coaches. I can add these, and make them ‘lists of…’ but I wasn’t confident that this was the best way to handle the relationship…
My User type definitely has Player and Coach as data fields.
‘Player Profile’, and then ‘Player Profile (Sport Name)’ was my attempt at capturing the unique details required for each sport.
Of course I could just put everything in a Player Profile, however, the fields would get to an unmanageable amount and wouldn’t be very neat.
The reason behind having different profiles for each role (player vs coach), is that again I was trying to avoid having a large amount of fields on a single table. @zhihong0321 suggestion to just have contract is kind of where I was going with the ‘Player - Club Relationship’ table, although by cutting out the profile this leaves me without the ability to capture deep and specific information tailored to each sport…
A person may exist in the app, without an associated user account i.e. someone has manually entered them in and they haven’t signed up.
A person may join and leave a club multiple times in their life, in multiple roles. For example, they have been with
– Club A 2005-2008 as a player,
– Club B 2009-2012 as a player,
– Club A 2013 as a player, and then
– Club A 2014-2018 as a coach.
We want to capture information about a players time in a sport as a whole, and at smaller levels (year on year, stint with team - what has been termed 'Memberships).
-Whilst some information will be consistent across sports (Wins/Losses/Draws/Win %), others will be different from sport to sport: Touchdowns scored (NFL), 2 Point Shots (NBA), 3 Point Shots (NBA). This is a fairly static element, so could be spelt out in a table, although this latest version moves away from sport-specific tables…
A person may play multiple sports, and therefore have wins/losses associated i.e. Michael Jordan has a range of data associated with his time as a Basketball Player (in multiple teams), and as a Baseball player). Likewise with Deion Sanders with NFL and Baseball.
A person may be a member of a Team that is not part of a club, e.g. Flag Football social team, and also a Club (which might have multiple teams playing in different grades).
The information to capture for an NFL player, will be different to a Basketball player. Clubs/teams will want to be able to specify different data points for players depending on the sport, and even then the same teams in a sport might care about different things. (my attempt at Entity-Attribute-Value tables).
There are some additional core tables that will need to exist that aren’t currently pictured, including Competition, Game, Lineup (among others).
There are no “foreign Keys” in Bubble. I think because of your background (great to see crowsfeet) you are forcing “relational” things into bubble. The ability of the Bubble db to have lists of “objects” is one of the most helpful things. If you are constantly navigating by doing searches on foreign keys then it is going to get pretty slow. Smallish lists of other data types would help a lot here.
i.e. Have a list of Sport Profiles on the Person. They won’t have thousands, it is great use of a list.
I would replace the Entity-Attribute-Value stuff with JSON text.
Membership <> Sport Profile feels like it is too complicated. I would be tempted to fold them into the same data type and use lists. I think I see what you are doing but it feels very relational in the structure.
The way to design Bubble tables is not from the data and CRUD perspective (as you would in Relational Modelling) but from a “what am I going to be doing 90% of the time” perspective.
Try to think about your most complex screen(s) - make your structure so that it is SIMPLEST to show your data on that, and not simplest from a CRUD perspective. It is always the lists that kill you.
So basically this. Where Bubble is “easy” is when you just navigate down lists using natural apostrophe language. e.g. The sum of a player’s list of clubs wins. Get this bit simple. The rest will follow.
Hi @NigelG - thanks for taking the time to write such a detailed response, hoping you might be able to unpack some of the following for me:
I would replace the Entity-Attribute-Value stuff with JSON text.
How would I do this? The way I started to create this in my app, before I stopped and moved to modelling, was to have a ‘Player’ table and a ‘Player Profile (Rugby Union)’ table. The Player table has a Player Profile (Rugby Union) field, and although I have it working it just doesn’t feel ‘right’.
membership <> Sport Profile feels like it is too complicated. I would be tempted to fold them into the same data type and use lists. I think I see what you are doing but it feels very relational in the structure.
How would I do this?? The problem I feel I’m encountering when I just have one Sport Profile, is that I can’t differentiate the different clubs/teams played at, the stats and info that relate to each stint.
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.
My initial thought was to denormalise the “sport profile” data and just have one big (and repeating) many-many-many thing.
I would also think about folding team and club together - understand that not all teams are part of clubs but I think it is going to make your “depth” many-to-many tricky if you don’t.
Thanks, the JSON stuff makes enough sense - I’ll do some research on how to implement.
Question about combining sport profile and membership - wouldn’t this mean I won’t have the ability to control and understand the relationship between a persons specific role at a club? i.e. requesting to join, have it approved, belonging to the club (accumulating stats, being invited to chats, events etc)… what does a many-many-many thing look like in bubble tables???
Similarly if I fold club and team, which way would I fold it? A fundamental difference between my app and other competitors is that in mine, players belong to a club, and they are temporarily assigned to teams on a weekly basis. Others you have to manually add a player to each team as you need…
Yes. The obvious benefit here (beyond trying to simplify getting to the name of the club/team…which is which you probably want most of the time) is that you can potentially add in a other layer to the group hierarchy if you ever needed to. And it should work the same without needed to add in a whole new structure.
As I said, mock up one of your harder screens with some test data and just see if the ‘queries’ on the data flow naturally.
While there is a lot more to much database speed (Petter Amelies’s ebook is brilliant) I am a firm believer in simple Bubble queries leading to good performance. If you are fighting your database at this stage it won’t bode well.
