Thoughts on a DB redesign

Fred
1 yr ago
23replies

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.

Database Architecture

- Alan_Cooke
- 1 yr ago
- Reported - view
Would be great if one could set up data models like in MSAccess.
- Mel_Charles
- 1 yr 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
- 1 yr ago
- Reported - view
Data models are fun:
- Rafael Sanchis
- Rafael_Sanchis
- 1 yr ago
- Reported - view
too complicated.
- John_Halls
- 1 yr 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
- 1 yr 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
  
  1 yr ago
  
  Reported - view
  Spaghetti Junction and the Aston Expressway, I remember it well!
- Fred
- 1 yr 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
- 1 yr 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
  
  1 yr 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
  
  1 yr 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
  
  1 yr 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
  
  1 yr ago
  
  Reported - view
  Fred Hi Fred This look great, you have some little example. I haven't even workday with pages yet.
- Fred
  
  1 yr ago
  
  Reported - view
  Thanks. I also haven't done anything real with Pages yet.
- John_Halls
- 1 yr 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
  
  1 yr 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
- 1 yr 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
  
  1 yr 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
- 1 yr 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
  
  1 yr ago
  
  Reported - view
  Hi Fred.
  
  One questions, this example works only in pages ?
- Fred
  
  1 yr ago
  
  Reported - view
  I don’t have any Pages, just dashboards.
- Rafael Sanchis
  
  Rafael_Sanchis
  
  1 yr ago
  
  Reported - view
  Thanks Fred, It took me a while to recreate it, I'm still trying
- szormpas
- 1 yr 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!

Content aside

1 yr agoLast active
23Replies
329Views
6 Following

Thoughts on a DB redesign

23 replies

Content aside

Overview