Table Occurrence Naming

[AndyHibbs]

Having searched the site first, I don’t believe any FileMaker based forum is worth its salt unless it has a good old disagreement about how to name table occurrences (TOs) in the relationship graph. I’ve been delighted to see some really constructive help being given on some current threads and thought that some within our family soup may not have considered the impact of TO names. If working with fellow developers on projects it is essential to have this as a firm agreement.

I’ve just finished helping a client migrate the data from an old system to a newer version both created in house. I constantly got caught out by duplicate table TOs named for example (actual names not used here) ‘Furniture’ (name of source table), ‘Chairs’, ‘Desks’, ‘Cabinets’, etc. all based on the ‘Furniture’ table.

Hence the inclusion of a reference to the source table name is essential to give a clue when accessing the TO from the calculation engine, easily identifying the source table in the graph, sort dialogue, etc.

Then the subject for most disagreement: the location of this reference. Mostly, the argument is whether should be at the beginning of the TO name or at the end.

For instance, if we have the following links between these source tables:
Order <-> LineItems <-> Product
Frequently used naming include:
Order <-> LineItems_Order <->Product_Lineitems_Order
or
Order <->Order_LineItems<->Order_LineItems_Product
or abbreviations such as (using the above as the basis for this)
Ord<->Ord_LI<->Ord_LI_Prod

Nailing our colours to the mast, we are firmly in the source table at the start of the TO name camp. We would actually name the above along the lines of Order <->LineItems2Order<->Product2LineItems2Orders (we prefer to use ‘2’ as reading the names in this format explains the path).

I’ve never quite had an explanation to the logic of doing this the other way around, but hope to have that corrected here. Our reason is purely practical, many dialogues do not show the complete TO name, which can get quite long following the above logic. Hence, having the source table hidden has never made sense to us.

If we had ‘ Order_LineItems_Product’ and ‘ Quote_LineItems_Product’ as separate TOs for the Products table we could only see ‘ Quote_LineItems_’ in the Sort or Export Records dialogs, we’ve no idea what the actual source data is. Reversing the naming order keeps both TOs together in any alpha sorted lists and we at least know that ‘Product_LineItems_’ is definitely going to export product data, albeit without the full name the context cannot be identified.

Although we are Mac people going back to 1985, we do most of our development in Windows and there are some dialogues that will not resize big enough (or at all) to display the full TO name, another reason to use our naming convention.

I’m not saying any of the above is right or wrong, it is the way we work and I’d love to see alternatives and the benefits of these.

[jwilling]

I like one of:

INV__Invoice --< inv_ILI__InvoiceLine --< inv_ili_PRO__Product

or if you don't mind longer child TO names:

Invoice --< Invoice_InvoiceLine --< Invoice_InvoiceLine_Product

I think a natural aversion to abbreviations is warranted, but when it comes to TO naming, I think you memorize the names very quickly. It's worth mentioning the system above works best with anchor-buoy and selector connector. That way you can always infer the TO name based on the layout's context: "I'm sitting on Invoice and i need a count of lines so... inv_ILI..."

I think the extra verbosity of appending the whole table name to the abbreviation makes the graph more readable too since you don't have to look up the abbreviations somewhere else, the TO names train you about the shorthand as you work.

EDIT: I won't fight anyone over it, but I really don't like the numbered prefix approach. Numbers are not intrinsically meaningful like abbreviations are, and they take up just as much space.

[Bobino]

As some of you know, I like to avoid abbreviations as much as possible.

The only thing I would say about one of the formulations from @jwilling is that I would capitalize the part of the TO name that matches the base table name. Giving something like this:

INVOICE --< Invoice_INVOICELINE --< Invoice_InvoiceLine_PRODUCT

Capitalization may have a specific meaning in your own system and this may not be suitable. I'm simply adding to the conversation. Anyone can use what they want. You will always find someone to disagree with you

[Markus]

in some (human) languages, people tend to write novels for the naming for a TO (-:

Since in some of the dialogs, space is limited (one can not read the whole loong name and chances are good to pick a wrong TO...), we prefer to have abbreviations.

As long as possible, we name tables as short as possible

adresses -> invoices

• AdrInv_ID (primary key Adr for getting invoices for a specific customer)
• AdrInv_GrpID (a group of customers are glued together, ie a construction company with several independent 'daughters' who will get a 'group-bonus'), etc.
• AdrInv (sometimes, there is nothing else than the primary key)
• Adr_Inv

We often do the work together with someone from the customer's company - so we have 'to find each other'...

MotherTable ... DaughterTable. We got solutions (foreign solutions, created by the customer, etc) where daughterTO comes first. Almost impossible to select in some dialogs - but in the relationship-diagram, on can pick a TO by typing the first character..

We try to avoid characters that are not available per single-key on our local keyboards - and we strictly limit the characters to 'standard ascii' (no umlauts, etc.)

[harvest]

@AndyHibbs

Our reason is purely practical, many dialogues do not show the complete TO name, which can get quite long following the above logic. Hence, having the source table hidden has never made sense to us

making guesses about what table I am hitting at is no fun

[AHunter3]

No spaces and no underscores. Simple plain language terms. Short enough and predictable enough to allow for keying in the TO name within functions and scripts and not having to go reach for the damn mouse. InterCapsAreGood.

[mipiano]

For me, readability and comprehensibility come first. A system of naming TO's is a skeleton for me to build on - not a corset that constrains me.

I separate sections of meaning with underscores. Within such a section, I use CamelCase.
But if, for example, the word "contact" occurs repeatedly in a name, it is more distracting than helpful in grasping it quickly. "ContactMethod" says enough; I don't need to write "Contact_ContactMethod“. If the TO is for creating contact methods, then I call it "Create_ContactMethod".

Abbreviations are ok if they can be understood by everyone without problems. Otherwise they are a horror for someone who might join your project or take it over later.

I try to find the best possible name for each individual case.

[mipiano]

Some of you have cited that long TO names are not fully displayed in some situations. This has never happened to me on the Mac. What dialogs or situations do you mean? Does this happen under Windows or also under macOS?

[AndyHibbs]

This is mainly in Windows. There are many window restrictions that do not apply to Macs,

[mipiano]

Thanks for your reply, Andy.

OK - that actually makes it difficult to handle. In fact, you can't know if the file will only be edited on a Mac or if it will also be edited on Windows at some point.

The question now is up to which length names are displayed completely.

[AndyHibbs]

@mipiano that’s a difficult one to answer, as we’re so used to it that we no longer take any notice. However, this isn’t the only reason we name the TOs commencing with the source table name, it just makes far more sense to us, but not everyone’s brains work the same. We still don’t completely understand the logic of ending with the source table name, particularly with long names.

From memory sort and export jump to mind. But other window issues include the script workspace. Double clicking a field or variable then opening the calculation window in front of that, it then isn’t possible to move the, say variable, dialogue below. If this is hiding information in the script that needs to be referred to, the only way of displaying this is to close the calculation window, which is a pain if half way through something, move the window and open the calculation window again. On the Mac, it is simply a case of moving any of the windows.

I think there are many who post who forget there are subtle differences between Mac and Windows use of FileMaker. We’ve always celebrated any software that provides cross-platform functionality despite the nuances between them (particularly in the dark days when Apple’s demise was predicted weekly).

Kind regards
Andy

[mipiano]

Thank you for taking the time to respond in detail, @AndyHibbs.

I will consider for myself how to reconcile my perceptual psychological considerations and the technical limitations. I find your advice very valuable.

Warm regards
Udo

[harvest]

my convention go like this:

The TO first has the table name of the referenced table
the reasons have been written down in this thread although for me the first one always has been that I don't like to look for the same table under different names. Line items it is and not address invoicelineitem invoice or worse.

The second part goes to the context or anchor

The third part varies in length from 0 to 60 letters telling about the path between context and referenced table where the tables are counted in the direction from context to referenced table and steps are numbered

The fourth part is used to name the field or sometimes the type of matching that starts the relationship
This can be the ID of the Anchor or a term like valid or active as a description for a relationship based on dates or flags.

It reads for me like
Lineitem connected to address via invoice by addressID
or in the TOG
LineItem_add_2_inv__pkAddress
or
Product connected to address via invoice via lineitem by addressID
which translates to
Product_add_2_inv_3_invltem__pkAddress

Those are reasonably simple examples but as we work on solutions with up to 5 colleagues for 15 years now and this still holds

Binding without spaces has the advantage of selecting the string with double-click.
3-letter abreveations had to be introduced because of a) Windows OS - FM interface shortcomings and b) 100-letter limitation in TO-names and c) are also used in fieldnames of the tables and d) as part of primary and foreign key names

As a side note the TOs also have a colour scheme where TOs of the same origin or module share nearby colours: all address tables have some shade of blue etc. This enhances readability of the graph once you get used to it. Every instance of the persons table has the same nuance of blue all over the solution.

some cents for the weekend
Holger

[steverichter]

@AndyHibbs

There are many window restrictions that do not apply to Macs

I'm using Win 10. Here's an example of window that CAN be resized. I'll check to see if any others cannot.

[xochi]

I tend to use a naming format like this:

[TableName] ["to"] [Relationship info]

where

[Relationship info]

Has as much detail as needed to talk about how the two TOs are related - key fields, sort order, etc.

So I might have a TO named

"OrderItems to Orders by ClientID Sort Date Reverse"

Which tells me this TO:

• base table: OrderItems
• related to Orders table
• by match key ClientID
• the relationship is sorted by the Date field in Descending order

Another example:

Employees_Import to Employees by FirstName LastName FirstRecordOnly

Which means:

• base table: Employees_Import
• related to Employees TO
• by compound match key FirstName+LastName
• FirstRecordOnly (a 1/0 field saying "this is the first instance of this First+Last name key in the table")

[bdbd]

We organize our relationship graph using a modified Anchor-Buoy technique. Before we go further, it helps to know how we name tables.

Each table name is a singular (as opposed to plural) noun or compound noun expression written using upper camel case. Use of short forms or acronyms is highly discouraged. Examples: Contact; Address; Invoice; LineItem.

There is a potential for the same table to exist multiple times in a multi-file solution. We prepend the file name to the table name in such cases to make each table name distinct within the relationship graph.

The left-most table occurrence (TO) name is the TO's table name. TOs to the right of the left-most TO start with the left-most TO name and end with the table name of the right-side TO. Examples best demonstrate what this looks like.

Lets say that you have a TO based on LineItem to the right of a left-most TO based on Invoice. The right-side TO name would be InvoiceLineItem.

Lets say the left-side TO is based on the Customer table, a middle TO is based on the Invoice table and a right-side TO is based on the LineItem table. The left-side TO name would be Customer. The middle TO name would be CustomerInvoice. The right-side TO name would be CustomerLineItem.

As you can see in the previous example, we omit intermediate TOs from TO names where intermediates TOs exist.

There is a potential for multiple TOs that are based on the same table to appear more than once in a table occurence group (TOG). We prepend or append a qualifier to a/some name(s) in such cases to make each TO distinct within the TOG.

For example, lets say the left-side TO is based on Person table, a middle TO is based on the Relationship table and a right-side TO is based on the Person table. Here, the Relationship table is a join table that documents the relationship between people. We could have the following TO names: Person; PersonRelationship; PersonRelatedPerson.

I am glossing over some details and nuances… but I hope this helps.

[AndyHibbs]

@steverichter and here's one that isn't - Export Records. The window can be resized and the 'Group by' and 'Field export order' will increase in width correspondingly, whereas the field name list remains the width shown.

At least with the source field as the prefix, I can identify that I will be selecting a field from the 'Address' table, if I 'Move' it to the right side I can then see if it is from the right context.. If I had 'Add' or 'Address' as the suffix, I would have no clue as to what I'd be selecting.

[Torsten]

@AndyHibbs, I am on macOS 11.2.3 and FMP 19.2. The left column expands here as well. Is the limitation you described specific to the Win version?

[AndyHibbs]

@Torsten yes, these are Windows limitations that most Mac users would not be aware of.

Kind regards
Andy

[Cecile]

Interesting, except for the --< symbols, in my personal projects I was using almost that same convention (caps for TO, camelcase for related) of your first example.
The asceticism of the conventions established by my business partner is too left brained for me (figuratively speaking). I see the value of the convention but for me it is cognitively too much to rebuild the "story" of the relationship. That has hindered my ability to hone solid relationship skills (no pun intended). It has had the unfortunate effect to deter me completely from the programming aspects of our development activities. I ended up developing a UX UI expertise which is most likely a better use of my talent anyway but it is still a bit of a sting to feel unable to move beyond that plateau.