Clarifying 1:1 intent

[LaRetta]

Hi everyone,

 

More and more I find myself moving less-used fields, or fields which group together, off into a secondary table based upon a 1:1 relationship (one-to-one).  Narrow tables load faster than wide tables particularly over WAN and, let's face it, it is the future and desktop is losing ground.  Everyone wants mobile and particularly, the ability to switch perspectives and tools (whether iPhone, iPad, desktop or web direct) because we are constantly on the move.

 

I don't know about you, but MY ONLY provider is the slowest in the United States (6 mbps download/.76 upload) ... so low that others laugh when they hear it and they usually send flowers.  :-/

 

I am not alone.  If you live in a big city, count your blessings.  We rural folks may have majestic mountain/forest scenery but we pay dearly for the privilege.  So, it is known that narrow tables are better than wide because most fields (except containers) are fetched by the client from the server and the more fields, the slower the transfer.  Such is life at present and most of us suffer from some sort of latency.  So when you must,  or when you decide to create a 1:1 relationship, what does it look like?

 

Attached is a graph with the top child relationship being a 1:1.  However, you can't tell it is 1:1 because there is a fork on the child side so the relationship will actually allow more than one child!!  We know this because the foreign key (in this case permitID) is not unique in the child.  So when viewing the graph, and specifically this top relationship, we do not know whether it is 1:1 or 1:n.  There is potential in future that, because it is unclear, a developer places a PORTAL on a layout to allow multiple child records.  This is bad.

 

I feel that, if you want to ensure a 1:1 relationship, you should go to the trouble of making the foreign key UNIQUE.  Notice the lower child, if the foreign key is unique, the fork turns into a line ... clearly indicating the 1:1 relationship.  This removes ambiguity and adds clarity to the graph, particularly for others viewing it.

 

NOTE: There are many reasons for wanting to one-off a group of fields.  In the example presented, there were two government forms, completely different from each other, which had to be specifically placed on a layout for printing.  But the point is ... if you wish for a 1:1 relationship, I think it wise to also make the foreign key (the parent key in the child table) unique. As is, you may depend upon the fact that you don't place a portal of that child table ... but who knows what happens to a solution down the road?  And the fork can confuse others reading your graph.

 

Sometimes it is the little things.  Others' perspective is ALWAYS appreciated.  :-)  Happy February!!

 

[Mark Scott]

Thanks for this, LaRetta!  

 

In addition to clarifying intent on the graph (unquestionably a good thing), it's an important logical data-model constraint that absolutely should be enforced in the schema through the UNIQUE validation you suggest.

 

Mark

[Darren Emery]

LaRetta - this is an interesting topic, and I believe I understand the point you are making regarding the details of the relationship.  What I'm not quite certain about is the paradigm behind the 1:1 relationship, moving the data to another table in an effort to provide improved speed and performance.

 

I know this is likely a very fundamental approach, but I am very new to developing for mobile, and would really like to learn more.  Can you point me in the right direction?  A search here on the forum for 1:1 relationship didn't yield much.   

[Wim Decorte]

What I'm not quite certain about is the paradigm behind the 1:1 relationship, moving the data to another table in an effort to provide improved speed and performance.

 

It's a pretty fundamental understanding.  In many circumstances, FM will load all the data in a record, even if it needs to display just one field.  So by offloading "expensive" (long text fields, container fields, unstored calcs,...) to a 1x1 table you can save yourself a lot of grief

 

Attached is a graph with the top child relationship being a 1:1.  However, you can't tell it is 1:1 because there is a fork on the child side so the relationship will actually allow more than one child!!  

 

I would expect a good naming convention to take are of this though.  Only a small subset of experienced FM devs like you would look at the line endings to see whether it is a true 1x1.

The fact that the graph is not a true ERD does not help in this of course.

[LaRetta]

I may not be able to find specific posts about it but it goes like this:

 

When FM server fetches records, it must send ALL fields in a record (except containers) to the client.  The more fields in a record, the longer it takes to send them to the client.  With LAN (local network), it isn't as big a deal because the speed is much faster overall but over WAN, every field adds weight.  So if there is a series of fields, such as historic fields or fields which aren't accessed often when viewing the 'main' record data, placing them in another table can stop their download until you wish to view them specifically.  

 

In the case where you want to view these fields, you would switch to a tab panel, slider or popover (or another layout/window) to see just those support fields.  There is no sense downloading those fields unless your User needs to see them.

 

Here is an extreme example (but one I use because of the speed increase on iPad):

 

You have a Contacts table with the contact's name and then a bunch of other information including a Notes field (with many paragraphs of data), and a container field holding the person's signature.  Most often, when a User is viewing a list of Contacts (from which to make a selection), all they care about are a few important fields such as their name, phone number, Type etc. so if you think ahead and place only the fields necessary to view in lists in the primary Contact table, it loads lightening fast.  You can view lists of Contacts with little overhead.  Then when you find the contact you want to see details on, switch to a layout which is ContactDetail ... where all the other data resides.  So what needs to be viewed in general 'find' lists would go in base table.

 

Place Notes into a shared table where ALL other tables can place their notes.  The foreign key in Notes would simply be called externalID and would hold the UUID from each of the main tables.  Rarely does a User need to see the notes so why download it; same with containers.  Many fields are informational only ... they are not needed for calculations or keys and by offloading them 1:1 does not hinder your solution.

 

The idea is to design narrow (few fields in a table) and not wide (many fields in a table).

 

I see Wim just mentioned offloading large fields etc so I think that covers it.  The reason I focus on taking advantage of 1:1 is because, even if designed for desktop over LAN, it never fails that the solution needs to move to WAN.  In fact, I now always design for fastest experience simply because, in this way no matter how being accessed, the solution is as fast as it can be and there is little repurposing necessary.  

 

I bring this all up because I was just handed a solution like the first child example.  I had to research and verify that 1) the child table didn't have duplicate id_permits, 2) that no layouts had the child table set up in a portal (it had allow creation on) and 3) all layouts displaying these fields were directly placed on the parent layout.  I am not convinced that naming convention solves the issue.  In this case, the parent is Permits and the child table is PermitConstruction.  That could be 1:1 or 1:n.  I had to further check that there were no fields in PermitConstruction which held a date or other indicator that it was the 'many' side to Permits.

 

I've seen this issue many times where the developer assumes that by directly placing the fields on the parent, no further action is required and, like Mark says, it is important to validate unique because one never knows what the future of the solution will hold.  It is best to make these decisions before building into your file.  Pre-thought and adding fields once (based upon your best guess) far outweighs creating swiss cheese by moving fields around later.  Yes, you will end up moving fields; rarely will you get it right the first time but you can also post your field names here and let us help you decide whether you can split some fields to another table.

 

Of course if you only have 10 fields in the table, it probably doesn't need to be split but if you have a HCFA-1500 form with 16 multiple parts totaling 180 fields, it is certainly time to split them off for sure, as well as notes and containers.  

[Mark Scott]

A search here on the forum for 1:1 relationship didn't yield much.   

 

Hi Darren,

 

Wim's "in a nutshell" description may be all you need (the gift of conciseness!), but, if not, then the term you probably want to search under is "narrow tables" or "narrow vs. wide."

 

hth,

 

Mark

 

[Edit:  LaRetta's thorough reply came in just as I posted mine.  Hopefully the next person who searches for this topic will stumble upon her post, and then will know all they need to know.  ;-)]

[LaRetta]

The example of list view ... if your Contact table is narrow and only has name, phone number, type (for example) ... and your layout displays 20 records ... it is much faster to fetch 20 records with 3 fields rather than 20 records with 250 fields, particularly when you don't want to see any of those fields - only use the list for selecting a specific Contact.

[LaRetta]

i confess that I get carried away.  

 

But folks can read it or not ... and folks can agree or not (and if they don't I hope they speak up).  All is good and, as Wim says, no worries! 

[David Jondreau]

There's (almost) always some tradeoff. Validation requires some resources at record creation. The bigger the total record count of a table, the more resources.*

 

I do not usually use record-level validation never for primary keys now that we have Get ( UUID ).

Also, I don't use naming conventions to distinguish 1:1 or 1:many, but I do use Notes on the relationship graph extensively. Commenting is super helpful.

 

*For Unique validation

Edited February 24, 2015 by David Jondreau

[Mark Scott]

Place Notes into a shared table where ALL other tables can place their notes.  The foreign key in Notes would simply be called externalID and would hold the UUID from each of the main tables.  Rarely does a User need to see the notes so why download it; same with containers.  Many fields are informational only ... they are not needed for calculations or keys and by offloading them 1:1 does not hinder your solution.

 

This is a great approach, LaRetta, and is an ideal candidate for SELECTOR-CONNECTOR treatment!!!  (For anyone not yet familiar with Selector-Connector, it's a graph-modeling approach codeveloped by Todd Geist and Jason Young that certainly has been generating some buzz as of late [for good reason!])

 

In addition to the narrow table benefit, another reason I like the shared Notes table (and am currently building it into a solution) is that, often, a Notes field is a place for documenting exceptions and deviations from business rules or outcomes.  For example, in a lab database, it may be used to document details of spoiled samples or failed assay (test) runs.  Putting those notes all in one place provides a one-stop-shop which a Supervisor can periodically peruse in order to monitor quality control issues and spot trends.

 

Mark

[Rick Whitelaw]

When I started with FM everyone had a phone number. A land line. Fewer contacts than have now had mobile numbers. I saw it as an advantage to have different tables for each. Phone and mobile. What remains true today is that few contacts have a website. This certainly deserves a separate table. The loading of a Contact record remains lightning fast using this idea to say nothing about complex calvulations, summaries or aggregate calcs (or finds). As well, though it's less important these days, a single Contact record containing only what EVERY contact has, such as an address, uses less disk storage than a Contact record that includes a pile of empty fields.

My two cents . . .

[LaRetta]

I do not usually use record-level validation never for primary keys now that we have Get ( UUID ).

 

To quote Jeremy Bante on the subject, "I tend to leave validation for uniqueness on anyway; there's nothing wrong with wearing a belt with suspenders (especially if they match). There is a performance hit, but I never found it to be a big deal unless you're creating hundreds of thousands of records at a time on a regular basis."

 

Simply, nothing is more critical than preserving uniqueness of the primary key.  I used to NOT validate auto-enter serials either but I began validating them unique and, just because we've switched to UUID, I still see the need to validate unique because we are syncing data from mobile devices, we are restoring data after a recovery ... and we count on that unique ID to stop duplicates from entering the system.  We all have seen systems where this has not been implemented and duplicates can cause major problems.  It is a small measure with a big value.

 

Example:  A power User deletes several records in Contacts.  Other User continue to work in the table and three days later they realize those Contacts need to be imported back into the solution.  If you use the import 'Update Matching/Add Remaining', you will lose more current record changes to all records because they will update with the old values when they add the deleted records back in.  However, if you use only 'Add New Records' but have validation unique on the primary key, duplicate records will not import preserving the other data changes and only the deleted records will reimport.  There are other such examples of when validation unique (always) comes in very handy on keys.

 

So does 'unique' have a cost?  Sure - but only during record creation or import and it prevents a far greater cost - duplicate primary keys.

 

I completely agree about commenting in the graph.  The thing is ... the graph is a representation of the cardinality of relationships.  I'm not sure that I agree that few developers are aware of the meaning of the line ends.  We can look at a graph and have a clear picture of the possible relationships and how data might flow up or down those TOGs.  That's why we're always asking to see someone's graph.  A fork means there could be multiple identical keys.  A line means it is unique.

 

Thank you for stepping in, David.  I always really respect and appreciate your input!  :-)

[LaRetta]

 I saw it as an advantage to have different tables for each. Phone and mobile. What remains true today is that few contacts have a website. This certainly deserves a separate table. 

 

Hi Rick,

 

Well, I have to say that the two examples you gave aren't ones I would select as justification to split tables. However, I understand what you mean.  This is why it is good to talk about these various bits of data amongst ourselves to get a feel for what can logically be split.  I think it is a shame that more folks don't 'head-trip' here on forums.  There is nothing wrong with empty fields - they are cheap - and it may appear that I am contradicting myself in saying such when I am discussing off-putting groups of fields but there is a balance we look for, for example:

 

So what fields could be split?  If you look at your layouts, you will sometimes have groups of fields placed in a tab panel.  These would be good candidate for splitting to a related table.  If placed in a 1:1 table, they can still appear exactly as they are on the tab panel but they will not be fetched until switching to that tab panel.  Users don't look at every tab panel nor slider nor popover so why load it by having that data exist within the primary table?

 

I appreciate you speaking up. :-)

[LaRetta]

This is a great approach, LaRetta, and is an ideal candidate for SELECTOR-CONNECTOR treatment!!!  

 

WOW.  I had not seen Selector-Connector yet!!  Surprisingly, this is a concept I've been working on and have two skeleton structures under this theory already but I kept hitting snags in moving forward with it!  I am tickled to see this, Mark, THANK YOU!  I can't wait to see how Todd and Jason have resolved my sticking points!!

 

In my models, this played very nicely also with transactional data-entry and I knew it was a good approach if I could get past my stumbling blocks with it!  In my book, this is a revolution.  I never liked anchor-buoy - preferring modified entity with specialty TOGs instead and this is exactly what I've been wanting for AGES!!

 

Thank you again for bringing it up!

[Rick Whitelaw]

Thanks LaRetta,

I think the kernel of what I really meant to say is in the phrase "a single Contact record containing only what EVERY contact has, such as an address". Though a quibble, this fits my concept of normal. Almost all my Contacts have a Canadian Social Insurance Number. A few are Americans with a Social Security Number. Separate tables. I must be a stickler for skinny tables. I use dozens of 1:1 relationships. Whenever possible. I have since I started. I should make a test table that's less normalized and see if I notice a performance decline. One certain disadvantage of my current approach is a very complicated relationship graph. I find myself, sometimes ages after having made any changes to a given file, revisiting the graph in an attempt to make it more intelligible!

Rick.

[LaRetta]

Neither do we want every single field in a separate table!  There is such a thing as too much of a good thing!  Narrow is good - skinny is questionable and gaunt is unhealthy! 

[Lee Smith]

WOW.  I had not seen Selector-Connector yet!!  Surprisingly, this is a conce...

 

I just finished watching it and of course will have to watch again to review some of the fine points. LOL

 

I watch the Session video by Todd Geist also.

[Rick Whitelaw]

Neither do we want every single field in a separate table!  There is such a thing as too much of a good thing!  Narrow is good - skinny is questionable and gaunt is unhealthy!

I disagree. A table should have a record ID and nothing more (no other fields) except what is absolutely essential to represent the "data point" . . . Sorry, couldn't think of a better term.

[LaRetta]

Absolutely right!  And ... who needs data anyway?  It's just stuff to track, for goodness sakes!  

[Rick Whitelaw]

LaRetta,

Exactly! Now if we could just get rid of those pesky layouts and scripts we'd have real efficiency! Ha.

[Tom R.]

I'm glad to find this discussion. I have a solution that catalogs different types of documents filed in county courthouses, such as deeds, leases, mortgages and so on. Each type of document has different data to include, so it doesn't make sense to have a document table that includes fields for all of the separate document types (for the reasons that've been discussed above). Right now, I'm using an entity-attribute-value approach with a table for documents and a connected table of attributes. I'm running into difficulty using the attributes in calculations, so was thinking of changing to a model with a documents table, and then separate tables for each of the document types, such as lease, deed, mortgage, etc. 

I'm unsure of how to set up the relationship. From the perspective of validating uniqueness, it seems like it would make sense to put an id_any field in my documents table, that would hold the primary key of the related lease/deed/mortgage record. That way, I only need to validate in the documents table and the related record's table. However, since record creation will be done on a layout based on the documents table, it seems like it would be easier to put an id_document field in the lease/deed/mortgage tables. But then, for instance, for a lease document I would validate uniqueness in the document & lease tables, but would there be a need to also check the deed & mortgage tables?

Thanks,

Tom