1

Time to create more work for myself...

Never to leave a functional DB alone, I decide to correct some poor DB design. As you can see in the picture, I break the tenant of not creating multiple fields that store the same type of data. Fields like Q_Rank_form and S_Rank_form or rqNum and rsNum or rqPen and rsPen. Same data type but varied based on what round was completed. I track three leagues so I had to create Rank_form  and Pen fields for each league. Not very efficient. I did it this way before I really knew what I was doing, but it is time to fix it.

As you can see at the top of the picture is my new child table, RoundAnalysis, that stores the data I need with a field called round that keeps track of the different rounds completed. I'm still working on some functionality so have not worked on presentation yet. Now I don't have to make any changes if the current league changes their format, or I start tracking a new league.

That means I can delete over 50 fields in the TeamEventSummary table and just create view elements that show the data I need to see.

I also had to redo a button so it can create the proper child records. It was originally over 300 lines and now it is only 178. My original thought process was to first figure out which league I was working in then do all of my functions. This process, while logical, caused me to have many duplicate pieces of code.

The button needs to create records in three different tables, now four. The old code had these three create() commands repeated for each league. Now I only have four create() commands and I differentiate between leagues in variables.

7 replies

null
    • John_Schaffer
    • 6 mths ago
    • Reported - view

    Impressive improvements!

      • Fred
      • 6 mths ago
      • Reported - view

      Thanks. Learning everyday.

    • John_Halls
    • 6 mths ago
    • Reported - view

    Super impressive Fred. What sport is this?

      • Fred
      • 6 mths ago
      • Reported - view

       Thanks. I track team show jumping. The three leagues are Global Champions League, Major League Show Jumping, and League of Nations.

    • Fred
    • 5 mths ago
    • Reported - view

    A small redesign like this would not have been possible without  Ninext project. I can see if a field is being used in other formulas (red) or views (green) before I delete them. Here is what I see when I open Edit Fields:

    You can see the number by each field that I will be deleting. If I click on the red 4 by rqNum I see:

    This field is only used by other fields in the same table. I can either fix the code in the other formula fields or since I'm going to be deleting those fields as well I can just delete this field.

    Compared to if I look at where r1Num is used:

    There are three other tables that have formulas that depend on this field, so I need to go fix them first before I can delete this field.

    • Fred
    • 5 mths ago
    • Reported - view

    Here is an example of how the new subtable makes coding easier. I have a field that looked like this before the new subtable:

    let t := this;
switch League do
case 1:
    switch 'Round Type' do
    case 1:
        first(TeamRiders.SeasonsActive.TeamEventSummary[Location = t.Location].r1StartOrder)
    case 2:
        first(TeamRiders.SeasonsActive.TeamEventSummary[Location = t.Location].r1Placing)
    case "Q":
        first(TeamRiders.SeasonsActive.TeamEventSummary[Location = t.Location].QtrStartOrder)
    case "S":
        first(TeamRiders.SeasonsActive.TeamEventSummary[Location = t.Location].SemiStartOrder)
    case "F1":
        first(TeamRiders.SeasonsActive.TeamEventSummary[Location = t.Location].F1StartOrder)
    case "F2":
        first(TeamRiders.SeasonsActive.TeamEventSummary[Location = t.Location].F1Placing)
    end
case 2:
    switch 'Round Type' do
    case 1:
        first(TeamRiders.SeasonsActive.TeamEventSummary[Location = t.Location].r1StartOrder)
    case "JO":
        first(TeamRiders.SeasonsActive.TeamEventSummary[Location = t.Location].r1Placing)
    case "1PO":
        first(TeamRiders.SeasonsActive.TeamEventSummary[Location = t.Location].JOPlacing)
    case "3PO":
        first(TeamRiders.SeasonsActive.TeamEventSummary[Location = t.Location].JOPlacing)
    end
case 3:
    switch 'Round Type' do
    case 1:
        first(TeamRiders.SeasonsActive.TeamEventSummary[Location = t.Location].r1StartOrder)
    case 2:
        first(TeamRiders.SeasonsActive.TeamEventSummary[Location = t.Location].r1Placing)
    end
end

    Since you can't put field names into variables, I have to create code that takes into account all possibilities.

    and now it looks like:

    let t := this;
first(Location.Points[TeamID = t.TeamID].RoundAnalysis[round = t.'Round Type'].startOrder)
    • Alain_Fontaine
    • 5 mths ago
    • Reported - view

    Once again, an illustration of the fact that, when designing a database, one should not hesitate to create more tables as soon as they make sense. That is, as soon as one can identify classes of objects, real or virtual, that share a set of characteristics. One, especially if coming from the Excel realm, could think that creating more tables means more complexity. This example by Fred demonstrates that, on the contrary, when done correctly, it allows to make formulas and procedures a lot simpler. 

Login to reply

Content aside

  • 1 Likes
  • 5 mths agoLast active
  • 7Replies
  • 117Views
  • 4 Following