0

Thoughts on a DB redesign

Hi all -

So I've been developing my team show jumping DB the last few years quite organically. I knew almost nothing about setting up a DB when I started and had zero knowledge of DB coding.

This is my current data model:

 What a mess. I wondered what it would look like if I started from scratch with what I know now. Well here is my first draft of a new DB:

 Not all tables are in the new model but the most important ones are.

This is one area I wish Ninox would spend a bit more time on. One I wish they would allow us to move tables around.

As you can see the big change is the moving a lot of tables into a child relationship to Leagues. All those tables you see under Leagues wouldn't exist without a League record (currently track two leagues (Global Champions League and Major League Show Jumping and will add the new League of Nations next year). All the tables you see that are outside the Leagues table are tables with data that can exist if a league exist or not and can be apart of different leagues.

One reason I started a redesign, is to make my stat dashboards/pages easier to create. I can create a reference field that I can select the League then I am automatically linked to all the relevant data.

My only hesitation is moving the data across and having everything link properly.

Anyways, thanks for reading.

23 replies

null
    • Alan_Cooke
    • 6 mths ago
    • Reported - view

    Would be great if one could set up data models like in MSAccess.

    • Mel_Charles
    • 6 mths ago
    • Reported - view

    Gosh Fred

    Those images look like route plans themselves of a showjump circuit..🤣

    Good luck with this. Big job but i can actually follow your revised layout 

    I'm too embarrassed to show you my model as that too has grown as I have learned !!!

    • Alan_Cooke
    • 6 mths ago
    • Reported - view

    Data models are fun:

    • Rafael Sanchis
    • Rafael_Sanchis
    • 6 mths ago
    • Reported - view

    too complicated.

    • John_Halls
    • 6 mths ago
    • Reported - view

     I come from a FileMaker background. The look of the relationship diagram there is totally in the developer's hands but even so, I have seen far, far worse than any of the above examples. It takes considerable determination to keep an RD looking pristine and it may be by design (pardon the pun) that Ninox have kept this out of developers hands.

    Regards John

    • Mel_Charles
    • 6 mths ago
    • Reported - view

    It kind of reminds me of this! (as being from Birmingham)

    However,  I did think in the likes of MS Access/Dataease etc that you could dragg the relationship scheme icons around to try and tidy it up tho.

      • John_Halls
      • 6 mths ago
      • Reported - view

       Spaghetti Junction and the Aston Expressway, I remember it well!

    • Fred
    • 5 mths ago
    • Reported - view

    Here is an update on my data model redesign.

    I couldn't have done it without Ninext as I can find all of the links I was going to break and point them to the new path as I moved things around. I see that I duplicated a lot of data before and now data is more accessible.

    Still a work in progress, but am liking the direction it is going.

    • Fred
    • 5 mths ago
    • Reported - view

    I think I'm done for now. I've removed unnecessary table links and cleaned up my dashboards so I'm using links instead of selects and everything is running smoothly.

    I can almost follow all of the links!

    If they ever give us public cloud users the ability to access different DBs then I'll need to redesign everything again. :)

      • Mel_Charles
      • 5 mths ago
      • Reported - view

       Very Neat! and amazingly for me even I can follow it!.

      How to you beat doing "selects" in table views?

      ie

      let myID := Custid;
      let myItem := 'Search Item';
      let myRef := 'Search Ref';
      do as server
          select JobDockets where Cust_ID_fx = myID and Desc like myItem and 'Order Ref' like myRef
      end

      • Fred
      • 5 mths ago
      • Reported - view

      You can use any reference link as long as you end on a table?

      For example in my Team Dashboard I already have a reference field to the Team table from where I select a team to view on my dashboard. So in one of my view elements I went from:

      do as server
    let curRec := this;
    (select SeasonsActive)[Team.TeamID = curRec.Team.TeamID]
end

      to

      if League = 1 then
    Team.Names.SeasonsActive
end

      Much faster and does the exact same thing as above.

      • Fred
      • 5 mths ago
      • Reported - view

      Another example of removing selects is in dynamic fields. In my dashboards I started by creating dynamic choice fields to Leagues with:

      select Leagues

      Which works. There are only 3 records so easy peasy.

      But now the dynamic choice fields all use:

      Dashboard.Leagues

      There is a 1:N link between Dashboards and Leagues.

      • Rafael Sanchis
      • Rafael_Sanchis
      • 5 mths ago
      • Reported - view

      Fred Hi Fred This look great, you have some little example. 👍 I haven't even workday with pages yet.

      • Fred
      • 5 mths ago
      • Reported - view

      Thanks. I also haven't done anything real with Pages yet.

    • John_Halls
    • 5 mths ago
    • Reported - view

    Excellent work Fred. Looks like a big part of your success is down to recognising and setting Composition correctly. You have some nested three (or four?) deep.

      • Fred
      • 5 mths ago
      • Reported - view
       said:
      Looks like a big part of your success is down to recognising and setting Composition correctly. You have some nested three (or four?) deep.

       That is correct. It all came from sitting in on a Nioxus Thursday and saw how they are doing their tables and it all started to make sense.

      I knew DB design to start with I probably would've ended in the same place. Like they say, a table is a child of another table if the records in the child would not exist if the record in the parent doesn't exist. I remember hearing that when I first started building but it never clicked until now. Took me this long to wrap my little brain around the idea.

      The Dashboard parent table breaks that rule. The child dashboard tables are not related at all to the parent. It is more of a organizational move.

    • szormpas
    • 5 mths ago
    • Reported - view

    Hi , congratulations on the successful redesign of your database!

    What impressed me that I couldn't think of is how you minimized the use of "select”.
    We can create a one-to-many (1: N) relationship between a Page (Dashboard) and a Table to access records directly using Dot notation links. Isn't that what you are saying? 

    Is this particular practice something that Ninox encourages?

      • Fred
      • 5 mths ago
      • Reported - view

      Thanks.

      You should always try to use relationship to get to the data you need instead of doing a select then filtering for the same records. For example, if you had a customer dashboard. You can have a reference field to customer. Then any other fields that need customer specific data can flow from the reference field

      Customer.Invoices

      instead of:

      let x := Customer;
select Invoices where Customer = x

      The first line Ninox knows to only get the related records. The second line forces Ninox to get all records from Invoices then find the ones that match your filter.

      If that is the only select on the dashboard that will work fine, but for me I was doing 20 - 30 calculations that had different filters and was running into performance issues.

      Now this is not always possible since not all Tables are related, but for me organizing the tables and then creating new data fields, I've eliminated bottlenecks.

    • Fred
    • 5 mths ago
    • Reported - view

    Here is an example of how to use relationships to fill in dynamic fields or views:

    Step 1: League is a dynamic choice field to the table Leagues.

    Step 2: Event is a dynamic choice field with the following code:

    let xL := record(Leagues,number(League));
xL.Seasons.Events[Year_calc = year(today())] order by -yearEOsort

    Oops, looks like I can make the above code better by moving the filter to the Seasons level. There are fewer Seasons records (one for each year) so it is better to filter at the level then at the Events level, where there are 16 records.

    Step 3: Select a Team is a dynamic choice field with the code:

    let xLoc := record(Events,number(Event));
xLoc.Season.ActiveTeams order by displayName

    Step 4: Select a Rider is a dynamic multichoice field with the code:

    let xSeaAct := record(ActiveTeams,number('Select a Team'));
if 'Full Rider List' = 0 then
    xSeaAct.'Team Membership' order by RiderNames.lower(LastName)
etc.

    There is more but this gets the point across.

    As you can at each level I'm using the power of relationships to get the data I need instead of doing selects. Well, first I'm getting the appropriate record from the selection using the record() command, but since I can pinpoint the record it is very fast for Ninox to get it.

    I hope this helps.

      • Rafael Sanchis
      • Rafael_Sanchis
      • 5 mths ago
      • Reported - view

      Hi Fred.

      One questions, this example works only in pages ?

      • Fred
      • 5 mths ago
      • Reported - view

       I don’t have any Pages, just dashboards.

      • Rafael Sanchis
      • Rafael_Sanchis
      • 5 mths ago
      • Reported - view

        Thanks Fred, It took me a while to recreate it, I'm still trying

    • szormpas
    • 5 mths ago
    • Reported - view

      I just wanted to take a moment to say a huge thank you to you!  Your post motivated me to redesign my main dashboard by using the new Ninox pages with the aim to minimize "select" use.

    As you suggest, although it seems counterintuitive, I created a reference from my main table to the dashboard.

    I was able to zero the use of "select" and all record() by taking advantage of the capability for filtering at each level!

    For example: 
    'main_table '[Id = 1] . 'sub_table' [field = "text"] . 'sub_sub_table' [field > 0]

    In my new Dashboard, the data appears lightning-fast!

Login to reply

Content aside

  • 5 mths agoLast active
  • 23Replies
  • 290Views
  • 6 Following