0

A trip down memory lane

I found an archive of my DB from April of 2020. Here is the data model:

 I had some idea of relationships. What I didn't know was not to try to do stats in your table views.

For example I did table view where I created formula columns to show my stats. Here is what my Riders table looked like:

 If I selected 2019 Stats view then I would see something like:

 Every column after Gender is a formula. I quickly was bogging down my DB where it would take minutes to switch views.

In February of 2021 my model looked like:

 I don't remember why I moved Results out of Team Membership, but I did. I still was trying to figure out stats as I still had all of the formulas in my table views.

In March of 2021 things looked like:

 I guess I learned about dashboards as you can see the three dashboards that got created. Still a mess and not very organized. Moving the stats to dashboards made the tables faster, but I still encountered long load times when working with data in dashboards.

Then in October of 2021 things looked like:

 I started tracking jumps at events (Course table). I added the Logo table as teams changed their logo through the years. Added the Seasons table which tracked years that a competition was held.

Couldn't find any backups from 2022 (not a good thing). So here is my current model:

 The idea of making almost all of my tables children of Leagues is asking yourself, "if there wasn't a league record would the other tables exist?" The answer is No.

I have solved my stat issue. I used to always figure all of stats from the Results table which has almost 8500 and growing. For example for Rider stats, I would filter all 8500 records for a rider then do the math. If I had a view element, then I could have over 100 riders that I needed to process those 8500 records over and over  for each stat.

Now I have stats at different levels up the chain. For example for a Rider they have their raw data in the Results table. The next level up is at the Team Membership table. There I do a summary of related Result records. Most of the time this is the data that I want to see most, so now I can just reference the specific year I want and the data gets processed a whole faster because only linked records need to be referenced.

Somethings that I didn't know I needed were tables to track name changes for teams and people. I didn't want to have to create a new record if a team or person changed their names. I would be nice to be able to do a full summary by looking for teamID or personID. Plus I need to track the name of the team in the results historically. Now that I'm thinking of it I guess I could have made the team name and rider name a text field and then I would have a historical record. Oh, well.

How have your DBs progressed?

3 replies

null
    • John_Halls
    • 2 mths ago
    • Reported - view

    Hi Fred.

    Thank you so much for sharing. This is so interesting to look at, not that I am pretending to understand your system. Are you able to share any of the UI as well?

    Regards John

      • Fred
      • 2 mths ago
      • Reported - view

      I'm not sure I understand my system half the time.馃槃

      It is interesting to go back and see how much I didn't know.

    • Fred
    • 2 mths ago
    • Reported - view

    Here is pic of how I was entering data into my Results table in April of 2020:

    As you can see I had manually select City, Team2, Riders, Horses.  This league had 16 teams at each event with two riders per round. So I had to do this 32 times for each event, 16 events in the season. Slow.

    A few things that I see now was that I was still linking to the City table instead of the Location subtable. In addition I wasn't using the Team Membership link yet, as you can see the empty link at the bottom.

    City data is limited to the City name. There is no year data or event order data. That data is stored in Location. By linking to Location, I don't have to worry about Year (which is a text field at this moment) or City. I was getting Event Order data from Location, but nothing else at this time.

    By using the Team Membership link then I wouldn't have to select a team or rider as that could be taken care of with just one click in Team Membership.

    But as you can see, months later I figured out Location was the better link. You can see the field Year_calc to show the year data from Location. But I did not implement the Team Membership link. 馃

    I also moved the Results table out from underneath the Team table.

    Now in March of 2021, you can see an explosion of new tables, but my Results table hasn't changed too much. You can see a new field called Ownership (to be later renamed Partners). That was created as I was having trouble figuring out a way to show only horses that the rider was jumped with. I can do it now, but I couldn't figure it out without a M:M table. Actually it makes more sense to use this table as now I can get data of partnerships easier with this table, since horses can move between people.

    You can also see that a new parent table called Country is now above City. I was manually adding Country name for each record in Rider. It took me this long to figure out that I should have a Country table to make my job easier.

    Sadly I still haven't implemented the Team Membership link.

    One thing that I did learn was to stop entering Results data directly in Results. Now I'm using the Location table to enter Results.

    This is how a Location records looked in February of 2021:

    Those columns in the Results view are probably the default fields when the link was created in Results. Not very useful.

    Now in March of 2021 a Location record looks like:

    So I have changed the columns in the Results view to show data that is more helpful. Which means that I probably started entering Results record from a Location record. This way I removed 1 step from entering a Result record. Baby steps.

    Now in October of 2021 I have finally implemented the Team Membership link, by calling the field TeamRiders. You can also see Partners has been implemented as well with the rest of the form cleaned up.

    Back in April of 2020, I had four reference fields that I had to select. Now I only have two, TeamRider and Partners. Since I create Results records from Location, that link is taken care of. The TeamRider link takes care of the Team and Rider link. The Partners link takes care of problem of only showing related horses to the rider.

    Just looked again and this Results form has not changed much since October of 2021.

Login to reply

Content aside

  • 2 mths agoLast active
  • 3Replies
  • 101Views
  • 2 Following