Best Practices for Table/Relationship Conventions

21

Sample size of one:

In practical terms, going to anchor buoy didn't improve speed, and in fact slowed down a few things that I had to refactor to get working at full speed again, but I kept it Anchor Buoy because I thought it would be easier... and if I'm being honest because I just spent 30 hours migrating everything over and I wanted that time to have been worthwhile.

In hindsight I wish I'd rolled back when the speed wasn't improved, but I know many people like Anchor Buoy and I will say one big plus is I can try changing the base table of a layout and sometimes get significant speed improvements, or use filtered portals in nifty ways... but overall it was much easier to do everything when the tables were one big blob and I only had ONE

Student => ClassList_Join => Courses

As opposed to also needing:

Courses => ClassList_Join => Student

And

Credits => ClassList_Join => Student

And all the others that creep up.

1 Like
22

Better... than WHAT? better than oatmeal? better than lego?

Reported to scale better than unified and better than selector-connector, both of which connect every Table Occurrence to every other Table Occurrence, triggering the path math.

That's an excellent observation. Do you know what that means for us? Does it have consequences or is it an artefact of the mode of representation you've chosen. In other words, why should we care?

One report (DevCon conversation) from a well known developer with a big system...

  • Unified to Anchor-Buoy = 2x as fast
  • Tested Anchor-Buoy to Selector-Connector = 1/2 as fast, AKA back to slow.
  • Back to Anchor-Buoy for the 2x faster result

We use Anchor-Buoy from the start because in small system speed is usually not a concern and they often scale up and we like it if they then run 2x as fast.

1 Like
23

I wish it were that simple. I've spent years looking at this stuff (pre FileMaker too).

I have one big system I rewrote from 2013 to 2018 (and before that FileMaker 6 to 7). It was originally Anchor-Buoy/multi-file, then rewritten as single file with multiple "islands" if you like.

I had the benefit of attending one of Ray's master classes and beating out the conversation around Anchor-Buoy (refer to the paper mentioned in an earlier post)...it was very interesting and I changed my thinking.

Some anecdotal observations about that big system of mine (I don't think that you can't just attribute changes to RG style, there are other factors such as themes, etc):

  • the solution is much faster now to start up (until I added more functionality to use up that extra time)
  • it is quite a bit snappier to use and is great over a WAN (the original goal of the rewrite)
  • I find the RG MUCH easier to manage, but that's me

I can't remember whether it was Ray or David Head who said "let FileMaker be FileMaker" and learn to use the two way relationships it affords (which Anchor-Buoy reduces), but there is something in that.

So multiple "islands" in the RG is more work, I think, though I think if done properly, it has a better outcomes for the solution.

I also have ideas about table name prefixes, but that's a different thread.

Rob

24

Indeed! I've spent twenty years working on other peoples' systems, big and small. All the way from "look at what I made" to systems designed by professionals for large scale usage. My takeaway is that Anchor-Buoy (A-B) is not the answer, it's Just Another Method (JAM).

A committed programmer can make A-B suck. I've seen Anchor-Buoy implemented in so many different ways, and implemented so badly. The worst example I saw was a system originally designed as a spawling spiderweb. At some point, the designer had been convinced that anchor-buoy was the answer to whatever problem they had. So they converted their graph to A-B. They did this by duplicating the entire graph for each major entity. They moved the table occurrence for that particular entity to the left of the sprawl. Hey presto, Anchor-Buoy! Obviously A-B did not solve their problem. It's JAM, after all. It did make things noticeably worse. All they really needed was to reduce reliance on summary fields, unstored calculations, etc., and make better use of scripting.

1 Like
25

nice expression!

r

26

I thought I read somewhere that Claris optimized that 2-3 years back and while it might still be an issue in some cases, it's not nearly as relevant as it was 5 years ago when people were converting to Anchor Buoy?

Does anyone else remember anything about that? I absolutely might be wrong about that.

27

I'm late to this thread and have scan read through it, so hopefully I won't duplicate some very valid suggestions above.

I believe we're in the minority here, but we always start our table occurrence names with the source table TO name and end up with the anchor table TO name. For instance, a TO based on quote line items, would be named QLI2Quote2Contact. The '2s' are historical and when pronounced 'QLI to Quote to Contact' makes sense to us. To re-emphasise, consistency is important, hence ILI2Invoice2Contact, or OLI2Order2Contact would be understandable variants.

Our namning convention is partly down to having been doing it this way for over 3 decades, but more so that, particularly in Windows, there have been, and I believe still are dialog windows that cannot be resized, hence using the converse of the above example of say Contact_Quote_QLI could appear in one of these window as Contact_Quot and there is no method of establishing the data source in one of these windows if it is at the end of the name. At the most basic level, if a layout is named the same as the TO it is based on, then we can immediately see what the source table is in browse mode within the Layout name displayed in the status area, but could not do so on longer TO names without going into layout mode.

Flexibility is important within the naming consistency. For instance, we have systems that have tables named 'Company' and 'Component', which with all the best will in the world, a 3 or 4 letter abbreviation results in 'Com' or 'Comp' for both. In this case, we simply accommodate longer TO names, often using the full table name if there could be any ambiguity.

There is no right and no wrong, as long as there is consistency throughout.

One other incredibly useful technique we use is to have a text note key in the top left of the relationship graph. For example, this could read:

1a - Contacts
1b - Companies
1c - Addresses
2a - Quotes
3a - Orders
3b - Remedials
4a - Invoices
5a - Purchase Orders
6a - Items
6b - Stock
6c - Goods In
7a - Human Resources
etc.

Each TO Group is separated from others by a text note containing one of the above values. Hence, if we want to go direct to Purchase Orders, as soon as the graph is open, we type 5a and are immediately taken to the correct TOG. Using this method we can jump around large relationship graphs. It is important to not start any text notes with dates or any other numerics, as it breaks this system. Hence, if we made a note we'd initial it first followed by the date and detail.

We come from an era where Claris/FileMaker Inc. published recommended naming conventions, which included a leading underscore for key fields. In the modern era this was as useful as using the '@' symbol as a find operator. Who was to know that email and SQL would be future features. Hence, we would not include any special characters in our table or TO names.

Regards
Andy

3 Likes
28

I can confirm that anchor/buoy is much faster than a selector/connector model. After the performance of my solution with S/C dropped drastically (especially in network operation), I converted everything back to A/B and since then everything has been running smoothly.

As far as A/B naming is concerned, I know and have to deal with both versions: Anchor_Buoy and Buoy_Anchor. Personally, however, I prefer Anchor_Buoy because I have the direction of view in the back of my mind: what's my source table and to what target table do I want to get to? E.g. I want to look from invoices into the line items: Invoice_lineItems
But I guess that's only a matter of taste.

2 Likes
29

How true. I love that!

I like A-B, for how easy it is to understand for other developers, including ā€œfuture meā€. :wink:

For naming, I prefer A-B order, and things spelled out with PascalCase for easy acronym type-ahead in the calc engine.

Naming things with B-A order is a good work-around for the FM Windows dialog ā€œbugā€ that has been around way too long. But thatā€™s one of the many reasons I couldnā€™t work on Windows.

30

Malcolm - the Schema file is where I manage all of the lookups, cascading deletes, and security. And it links to four distinct Interface files (each its own stand-alone app). I only ever want to define the cascading deletes once. The Schema file is where I try and represent as much as I can of my data model. But more importantly, dividing the TO relationships between the Schema file and the Interface file lets me better manage complexity. The former is all about the data model while the latter is all about the interface and things like record creation.

It's also worth noting that I don't link all my TOs together, so it's hard to compare to either a pure AB or SC model. I instead break them into function-based TOGs that are leverageable in other solutions. Indeed, ever since FM12 and the introduction of eSQL, I have defaulted to only creating a relationship where I know I will need/strongly prefer one (usually for things like portals, record creation and relational integrity management/cascading deletes).

By not automatically linking all my TOs my solutions typically have a TO-to-base table ratio of around 5 to 1, and that includes the duplicate TOs one gets from using a separation model. If I didn't use a separation model, the ratio usually goes down to less than 4 to 1, and in some apps, as low as 2 to 1. The last solution I created that used strict AB conventions had a TO-to-base table ratio of over 10 to 1.

I've always assumed that this "selected but not completely connected" approach helps performance relative to both AB and SC (although I haven't been very scientific in measuring this). But just as importantly, this kind of structure has made it less likely that I will run into my biggest challenge of all - excessive complexity that requires more of my time to manage. Alas, my brain has far fewer available processing cycles than my computer does.

Cheers

31

Right. It makes sense to include relationships needed for the deletes, and if you're using lookups to acquire data then they need to be able to find things (I'm more in favour of scripting data inputs than lookups). The slimmer you can make the data file the better. Each relationship forces an index to be built, and they have to be loaded when the file is opened, so reducing the number of indices in your data file will give you better performance in all of your UI files.

32

In this presentation by @WimDecorte, the topic of using _suffixes, for example, _acd on Table Occurrence names was mentioned.

Provides increased code readability when using the popular Anchor-Buoy method of managing the Relationship Graph.

Have a look. It might make your day!

image
image1284Ɨ920 90.4 KB

1 Like
33

Each to their own; in my case, articulated rationale for each naming decision, has been the gating criteria for consistent (and evolving) usage over time.

TOGroups are named starting with a "T", and a 2 digit number unique to that group. It is suffixed with an "a" (parent), "b" (Child), "C" (grandchild), etc.

This eliminates the problems of >1 TOG using a prefix of say "COM" when more than one TOG deals with a base of company.

It also forces alpha sorts in TO selections into discernible groups.

The base table in the name is ALWAYS ALL CAPS
The related table is always lower case.
A relationship identifier is placed between table names, separated by a tilde. Typically with is ~id~, but other natural key relationships and global match fields. show with something other than ID in between

The result looks something like this.

T04a_COMPANY
T04b_company~ID~EMPLOYEES

Now I know 1) what group this is, how deep in the relationship is the TO, and how they relate, without even having to go to the relationship graph to figure it out.

There might be a TO5a_COMPANY group to deal with a different need,

And sometimes there is a suffix on the TO name "|ctr" denoting this is a "create through relationship" TO

On large solutions, I also create a TO that might look something like:
"~~~~~~~~ TO4_CUSTOMER~~~~~~~~~~~~"
To produce a quick visual break in the TO list by TOG.

Also in my relationship graph at TOs that start with "U" and not "T". These are utility layouts, never to see an end user interaction.

ALL base tables start with a "z_", so that they sort to the end of the list, are noted as base tables never to have relationships. They are visually grouped on the relationship graph in functional affinity groups.

The combination of this meta-data within the naming convention, saves a huge amount of time in TO understanding and selection, plus greatly reducing errors.

My 2Ā¢ worth

34

Thanks this is great. Things I love about it:

  • z_ for every base table and NEVER having a relationship. I wish I'd don this.
  • Numbering - Emoji's are easier for my dyslexic brain to handle, but the concept is exactly what I'm looking for. I might be missing something but the "T" feels redundant in this case. It seems like everything would either start with z_ or T, but it could just as easily be z_ or the number (01, 02, 03, etc.). Are there other TO types that you have?

I'm also curious how you handle the identifier since the name often won't be the same in both tables. In the example you showed it says "T04b_company~ID~EMPLOYEES". What happens when the two fields aren't the same name? I'm assuming if it's ID or PrimaryKey it's pretty straightforward, so for this join:

Student::PrimaryKey = StudentCachedData::04Student_PrimaryKey

You'd say "T04b_student~PrimaryKey ~STUDENT" and it's clear from context.

But it seems a bit more complicated in cases like this:

Student::FullName (i.e. "Kirk James" IDK your last name so I made one up)
Student::FullNameExpandedForSearch_c (i.e. "KĀ¶KiĀ¶KirĀ¶KirkĀ¶...")
Student::FullName_ActiveOnly_ExpandedForSearch_c (i.e. "KĀ¶KiĀ¶KirĀ¶KirkĀ¶..." if you're active, blank otherwise)
Student::SearchField

I then have two separate joins

  • Student::SearchField = Student::FullNameExpandedForSearch_c
  • Student::SearchField = Student::FullName_ActiveOnly_ExpandedForSearch_c

Do you then name the Join Field something like:

04SearchField_FullName_ActiveOnly_ExpandedForSearch_c

What if that same field is also joined from 01? I feel like the numbers don't hold up in this instance...

35

~id~ is indicative of a primary<>foreign key relationship

The relationship identifier is more categorical than specific. If ID then you know if is on keys; if it is anything else ( e.g. ~gm~ = global match field) then you may have to go look it up in the relationship graph to get specifics. But at least you have that indicator. That meta data is invaluable for accurate selection, and the bigger the application, the more prevalent this is.

( I have one client written application with over 2,200 TOs. They did NOT use this convention and tracking down errors in layouts and fields is a nightmare )

The numbers hold up as T04 and T01 are completely separate TOGs. Might be the exact same parent Ā» relationship Ā« CHILD but the 2 digit TOG unique identifier, nails the context.

I also have a utility I built that opens a card from any layout and displays the FQFN of every field on a layout. With the TOG designator as a prefix, it is really quick to visually identify fields from the wrong context.

Natural keys - something I try to avoid - are infrequent, but can be categorically descriptive. In your example ~ActiveName~ .... the related tables provide context. Doesn't have to be specific - it is a developer aid, not a definitive description.

In my relationship graphs, there are typically 3 prefixes

Tnn - for table occurrence groups, where nn is the numeric TOG designator, and the T showing it as a table used in UIs
Unn - for table occurrence groups that are utility only - things like setting up a key pair table cascading value list, or preference data, or .......
z_ - no number, just base tables. Typically oriented on the top left of the graph, in affinity groupings for quick Opt-Click..drag addition sources.

T's sort to the top, U's are next, and z_ are always at the bottom. Makes selection quicker.

36

There is an entire related construction theory on field naming conventions, that plays into this TO naming convention.

There is also a script comment identifier for various situational script activities, that support multi-user development by flagging items for review, break structures in code, etc.

The one thing that I have NEVER gotten right yet, is a clear, logical naming convention for script names. Someday, that light bulb will go on ( or someone will share some brilliant approach), and that will turn into something useful. At this point, I usually end up changing script groupings a few times over the course of development, lacking a cohesive approach to script naming and organization.

37

Awesome makes sense. I was struggling with why you use a "T" at the start, then I remembered table names can't start with numbers... :slight_smile:

38

RE: Script names
I totally get that. When starting a project I've seen teams get stuck in loops trying to decide if they should start with a shared doc, or a trello board, or pen and paper, or a calendar, and my response to that is always "the first thing you try won't be right, and the second probably won't be either, so just pick one and expect to change it later"... I feel like this is one of those situations where have to start making the scripts and then see where they go. I have some standard folders (Dev Playground, Debug, Reusable), but other than that it tends to bounce back and forth between "by function" and "by layout" and "by TOG"... lol

1 Like
39

Tnn, Unn, z_ - also have, but not transitioned to, a core data relational model, with some other prefix ( Y_ maybe - need to sort AFTER T but before z_ TOs) - a set of TOs that define the basic relational construct of the solution, with no UI. this is really useful for transitioning to a separation model as that TOG is the basis for the core data.

LAYOUT NAMES

With the recent addition of the "Open Quickly" (Cmd-K on Mac, Ctrl-K on windows), adding the Tnn to the end of layout names, and to scripts that are context dependent, has been an awesome tool.

LayoutName: Company_edit_T00

Now if I am working in a TOG, finding all the layouts from that context is as simple as a keypress. Same with finding scripts that are specific to a TOG.

Also UTIL script gets that suffix, and MODular (context independent) scripts get that suffix.

Its been a great productivity boon, that I am still transitioning into.

40

This is excellent advice for just about everything, dev or otherwise.