Best Practices for Table/Relationship Conventions

[JasonMark]

1. The most important part of a naming convention is to use one.
2. The second most important part of a naming convention is that it be consistent.
3. The third most important part of a naming convention is that it be easy to read and understand.

Source: this thread on naming conventions from 2021

Please share your personal naming conventions for Table Occurrences if you're comfortable with it, so I get some different ideas.

At this point changing any naming of my table names or field names will be very problematic due to SQL, but I should clean up my Table Occurrences in my relationship graph.

SideNote: I'm dyslexic so I try to use emojis wherever it won't break things (not in table names of field names b/c SQL doesn't like them). It helps me a LOT to have them in Table Occurrences and script names.

[JasonMark]

FWIW My current naming convention (Anchor/Buoy) is:

Student_Globals_Staff_WhoAmILookup

But I'm considering these alternatives:

WhoAmILookup_Student_Globals_Staff vs
WhoAmILookup_Stu_Glo_Sta vs

[Malcolm]

I try to use emojis wherever it won't break things (not in table names of field names b/c SQL doesn't like them). It helps me a LOT to have them in Table Occurrences and script names.

Unfortunately those are exactly the places you will need to reference for sql. The base table names are never exposed outside the relationship graph (RG). You can only address table occurrences.

FWIW, I use lots of notes within the RG. If there’s any need to explain, the notes explain why a relationship is what it is.

[JasonMark]

Crud. Yep that broke everything. I just reverted... oh well so much for my new, neater, layout and naming convention. Thank's for the heads up.

[JasonMark]

what do the notes look like and where do they show up. I'm actually very rarely in the RG, which is why I never bothered cleaning it up before. 99% of the time when I'm looking for a relationship it's when I'm adding a field to a layout... which is why I liked this new model of looking at things. It was super clear what I needed. Will these notes that you mention show up there, or only in RG?

[jwilling]

100% agree consistent naming is incredibly important. It makes a system sooo much easier to maintain.

I'm a fan of the 3-4 letter abbreviation per base table, mixed with anchor buoy. And then some capitalization to make it extra clear which base table a TO represents

CUS = Customer
INV = Invoice
ILI = InvoiceLine

And then you might have TOs like this:

• CUS__Customer (Customer anchor)
• cus_inv_ILI__InvoiceLine (buoy TO based on InvoiceLine, two hops away from CUS__Customer)
• inv_CUS__Customer (Customer buoy related to INV__Invoice anchor)

Occasionally you might have a specialized relationship that warrants an extra name tag and for that you can do this:
cus_INV__Invoice__paid, where the relationship is something like:

CUS__Customer --< cus_INV__Invoice__paid
    id == customerId
    ONE == isPaid

[Malcolm]

Notes look like this:

You've used them to label table occurrence groups: phases, student, tasks, etc. I use notes in this way too.

However I also use them to document the graph. The character limit for these notes is reasonably large. I haven't ever bumped into it. My longest notes are probably a thousand words, though most only need several dozen words. I usually describe the need for a note (because most relationships are self explanatory), the reason for creating the table occurrence, (or the group), and any layouts or scripts that are associated with it.

[JasonMark]

gotcha, is there some way to lock the notes? I keep selecting them by accident and they make it cumbersome to work around.

[JasonMark]

Appreciate this feedback and I love this concept, but now that I'm seeing examples I realize I'm just too darn dyslexic to be able to handle it. I need word shapes with good spacing to read things and wit this everything is "about" the same shape.

I really wish Emojis could work.

Question 1: Is there a validity to having a graph occurrence of each table with it's ACTUAL name for SQL, and then just keeping the relationship graph for internal FM stuff?

Question 2: Is there some way to do a complete find and replace in Filemaker? Because there is SQL within scripts, and custom functions, and on page layouts, and in calculations and lookups it's not trivial to try to find them all and change them in FM Perception.

NOTE: At one point I started using getFieldName like you suggested but that broke my brain unfortunately and there was already so much legacy code I couldn't see the benefit of spending a lot of extra time to still have it not be clean...

[Malcolm]

@JasonMark

Question 1: Is there a validity to having a graph occurrence of each table with it's ACTUAL name for SQL, and then just keeping the relationship graph for internal FM stuff?

Absolutely! It will create extra items in the graph but if it means that you can work better then it will be worth it.

Create new table occurrences and give them the simple SQL safe names. You don't need to use relationships between them for SQL.

@JasonMark

Question 2: Is there some way to do a complete find and replace in Filemaker?

Dream on!

@JasonMark

At one point I started using getFieldName like you suggested but that broke my brain

Sorry, there's no shortcut for that. You can (a) name an object once and NEVER change it. Otherwise you need to obtain the name that the field has at the time the script runs using GetFieldName.

[JasonMark]

Thanks!

[LVZ]

I use the Anchor - Buoy model and have own name convention that I uased also to teach in my courses (in German).
So e.g. the table [Persons] has all fieldsnames starting with PE_ … the primary key would be «PE_Persons_ID_PK» – I told the people, that it doesn't matter how long a fieldname is – important the you KNOW what is inside.
A field with a formula would end by … _ft (for a text formulafield), … _fn (for number)
The first table occurence has in this example a underline starting the name, i.e. _PE_Persons
And the buoy TO get a name you can easely understand by reading it.

[JasonMark]

Thanks. I like the idea of "something" in front of base tables so they're grouped together.

[flybynight]

I love discussing naming conventions! To each their own, but it is difficult to work in a system that uses a convention that you disagree with.

I'm a big fan of anchor buoy. It's just so easy for another developer to step in and know what's what.
As for the actual naming, I don't like abbreviations for table names. It's too difficult to tell at a glance what path a TO name takes when you have to decipher each step in the path, possibly cross-referencing each one. And there really isn't a need to abbreviate, unless you are on Windows… but I content that the limitations where dialogs don't show the full TO names are a FileMaker for Windows UI bug that is long overdue.

I like using PascalCase for both readability and type-ahead. I'm not a fan of camelCase, as the lower case starting letter bugs me.
So a TO name might be something like:
Invoices_InvoiceItems_Products and can be typed in the calc dialog with iiip. If there happens to be another TO with the same starting initial/caps combo, you will at least get it down to a manageable few in the drop-down.

We used to use all caps for the base table in the TO, so: Invoices_InvoiceItems_PRODUCTS, but I don't like that, because:

• A list of TOs in a picker dialog or auto-complete drop-down won't line up nicely if one of them in the path is all caps. It's more noticeable in a larger list, but you can see it here:
  • INVOICES
  • Invoices_INVOICEITEMS
  • Invoices_InvoiceItems_PRODUCTS
• It also messes with the auto-complete to not have the caps. For instance, with Invoices_INVOICEITEMS, you can only type ii and that probably has a lot of other matches. But Invoices_InvoiceItems gives you iii. I can't think of specifics now, but there are times when there are multiple compound word base tables for the last bit of the chain, and with that in all caps, you can't get there as fast.
• It's just not necessary with anchor buoy. You always know that the last table in the string is your base table.

Now for cases, when you have multiple buoys with the same base table, but a different use case, you need something other than _ as your delimiter. We used to use a double underscore, but that gets really long if you have multiple instances of that in your path. We didn't want anything that wasn't SQL-friendly, even though we always protect that with some custom functions that use GetFieldName and Quote. The most recent thing we settled on was a period, so: Invoices_Addresses.BillTo and Invoices_Addresses.ShipTo …but with so much JSON, I don't love having periods there. Perhaps the pipe | character?

It's also tough to maintain consistency when you are re-evaluating best practices.

For field names, again I like PascalCase, with a suffix for things like globals _g, calculations _c, summaries _s, etc. I don't like going overboard here with field or calculation types in the suffix, as I think it get to be a bit cumbersome.
For primary and foreign keys, I prefer the same primary key name for all tables. I know what table it is from by the table name, so it always seemed redundant to have that in the field name.
In some of our systems, we have keys at the top (fields in Manage Database are always alphabetized) with __kp_ID and _kf_ContactID, but we also have systems that use ID for primary and ID_Contact for foreign keys.

I've always wanted to have a roundtable discussion/debate on naming conventions at Claris Engage. I think that would be fun!

[Dutchman]

This thread is very timely for me, and coincidentally I just reread Ray Cologon’s 2009 white paper on Graph Modelling. It’s striking just how relevant this paper still is today. Like many, I started using Anchor-Buoy and appreciated how it helped clarify things in terms of naming, but after understanding Ray’s criticisms of the AB model (mainly the extra work FileMaker has do to resolve all of the superfluous TOs when opening a file – which can be especially problematic when relying on PSOS calls) I began using more of a functional island/selector-connector approach which can look anchor-buoy like at the “island” level, but in fact does not use the prescribed AB naming conventions, and does not adhere to the AB rule that every table has its own TOG where it is the base table.

Additionally, I now use a table naming convention that focuses on the function of each table and TO (and also fields, custom functions, value lists and calculation and script variables, as well).

A typical, modestly complex solution for me will be separated into 2 distinct files: one for the Interface and one for what I call “Schema Tables”. Within each file, I then prefix all my table names with a code that describes their function. When sorted by table name, this allows me to easily segment my tables and see very clearly “what is what”. More significantly, it makes managing security and the user privilege sets vastly easier, as it’s quite obvious what the function of each table is, and who should have access to it.

Interface Files use the following prefixes for its table names:

_INTERFACE: Contains the global fields needed to control the interface and record access. (note that this is the only table that uses a leading underscore, which keeps the table on top of all the others when sorted by table name).

ADMIN: Administrator Tables: limited access (for administrators only) which control value lists and user accounts & privileges.

DEV: Developer Tables: limited access (for developers only) to help support the interface (e.g. look-up data for things like addresses).

LOG: Log Tables: limited access (for developers only) tables tracking sessions, scripts and errors.

For the Schema Tables file, the tables use these prefixes:

ENT: Entity (or "Core Data") Tables: the data that the database is meant to track and will belong to the client (as opposed to the interface file, which is my property that I will license to my clients but ultimately belongs to me.)

JOIN: Another type of Data table that links two or more ENT tables/table occurrences.

SYS: "System" tables are data tables that are the rough equivalent of FileMaker's add-on tables. For things like notes, attributes, etc., that can be linked to other "Entity" tables and helps me reduce field-level duplication (for example, having a "notes" field in each individual table).

Here is a good example of a table listing for a typical Schema Tables file:

Making clear whether a data table is a core “entity” versus a “join” versus a “system table” versus a “DEV” table or “Admin” table makes it much, much easier for me to manage my RG, and I use colors to reinforce these naming conventions. Thus, “entity” tables are all one color, where “join” tables are yet another color, and so on for each of the above prefixes I’ve listed.

On the RG, I always keep the exact base table name to the left immediately after the prefix, and anything that follows this is a description for what that table is used for. For example, for master/detail layouts in a contact database, I’ll have things like “ENT_Organizations_Portal” versus “ENT_Organizations_Selected”. I only ever use underscores to separate the terms (never periods as they can seriously complicate using the Execute DataAPI script step). And like FlyByNight, I prefer Pascal Casing over Lower Camel Casing.

Here's an example of how all of this would look on the RG within the interface file:

Note that the above is just one "island" within a bigger group of islands. You can see this below:

Note the legend at the bottom.

I also use prefixing for field names (a separate topic which I am happy to write about), custom functions (I always want to know whether a function is native to FM or something that’s been developed separately, and this eliminates the risk of FM one day creating a native function with the exact same name as one of my custom functions), value lists (whether a list is returning the contents of a Key Field versus “regular” data), and finally calculation and local/global script variables. Prefixing each with a short description of its function within the database helps me enormously.

I hope this is helpful, and thanks to everyone for contributing to this thread. I always learn something new here.

Peter Gerlings

[JasonMark]

The idea of naming by type makes a lot of sense to me and your categories are pretty straightforward. I do wonder if the ENT prefix is necessary, but for alpha sorting it does make sense. But Log
I use PascalCase, but didn't realize it had a different name than camelCase lol.
I LOVE that you keep base tables separate for SQL.
I also love that you have one table for addresses, documents,

Clarifying question. Am I understanding correctly that you have two filemaker files. One that users open which is joined to the actual data in a separate filemaker file, and which permits you to keep your data schema so tight?

If I'm understanding it that's a great concept as I find Anchor Buoy to be cumbersome and much confusing than the spider web I used to have. I switched because I saw some tests that showed Anchor Buoy to be faster, but after spending a week converting a database and running it wasn't and now I have a lot of sprawl.

In terms of periods, if you have all your base tables separate for SQL, periods seem fine, and in some cases make it easier to double-click and copy-paste things (and in some cases make it harder I'm sure).

SIDE QUESTION: I find the sticky-note style notes in the relationship graph helpful, but cumbersome as I'm always selecting or deselecting them by accident. Is there some way to lock them? or is there some trick to not be constantly fumble when selecting things around them?

[Dutchman]

Dear Jason,

You are correct in that the ENT prefix is really only needed for the sorting benefit, but I also feel compelled to have a prefix of some type for internal consistency.

I have been using dedicated base tables for eSQL for a several years now, and it makes my code sooo much cleaner. Same for field-based value lists. I don't mind the extra TOs, and since they are not connected to each other, I don't think there's much of a performance hit to have those extra TOs.

Thanks for the validation that my single, dedicated tables for things like documents makes sense. I'm a solo developer, and I always have this nagging fear that what works for me would make no sense to someone coming from a different perspective.

As to your questions, I typically use two dedicated files. The Schema Tables file will of course have its own RG, which you can see here:

One additional point worth making: the Schema Tables file is what comes as close as FileMaker's RG can to matching my data model (i.e. a proper ERG). It handles all of my relational integrity needs (e.g. cascading deletes).

As for the periods, it's the ExecuteDataAPI Script Step that has issues with periods. When configuring the JSON for the inputs into the script step, having periods in my field and table names caused me no end of misery.

Finally, I wish that you could lock down the sticky notes on the RG. It's one reason I put the legend all the way at the bottom.

I hope this helps, and thanks for taking the time to reply to my post. As I said, the timing of this thread is perfect. Best wishes for a fine rest of your day.

[Malcolm]

@Dutchman

I typically use two dedicated files. The Schema Tables file will of course have its own RG

Why does your schema file have such a complex graph when you have an interface file? You could theoretically have a schema tables file which doesn't have any defined relationships.

[tonywhitelive]

Hi All,

I see Anchor-Buoy mentioned...

There are credible reports that Anchor-Buoy scales better...

While we see Table Occurrences (boxes) and Relationships (lines), there are also "paths" (see below).

While Table Occurrences increase linearly, "paths" increase faster (closer to factorially).

Something to factor in a you sail along on your FileMaker paths?

[Malcolm]

@tonywhitelive

There are credible reports that Anchor-Buoy scales better...

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

@tonywhitelive

While Table Occurrences increase linearly, "paths" increase faster (closer to factorially).

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?