1-1 Relationships in Filemaker

[Himself]

I have been pondering this question on and off for a bit now and have decided to get a consensus. I use relational database design and I like to normalize. Filemaker seems to do a better job of only using memory when there is something to store, better than other DBE’s that is. So I am wondering if there is any benefit to creating a 1-1 relationship in Filemaker. For example I have a system where records are created and the user can opt to include an attachment or not, it’s not required, do I just add a container to the main table or create a 1-1 relationship to another table which stores the attachments. Habit has me creating a separate table since not every record is going to be getting an attachment and I don’t want to store null values but I am beginning to think that Filemaker would like it better if I just added extra fields to a single table since it handles memory differently.

What are the pros and cons in Filemaker for each case in this scenario?

Is Filemaker faster with less linked tables? Slower?

Does Filemaker indeed not eat up memory for null values?

Any expert advice is much appreciated.

Thanks

[jamesducker]

I'm not 100% sure of the technical answer but my experience would suggest that null values don't take up much, if any, memory.

If you are storing containers as embedded files rather than file references, and it is going to be a frequent occurrence, then I would suggest not only storing them in a separate table but in a completely separate .fp7 file.

Having the main .fp7 file free of embedded containers keeps its file size down, which is always a good thing. You can then institute some other backup/archiving policy for the containers.fp7 file.

Hope that helps.

James

[LaRetta]

My prior stance would be similar to James but now I'm not so sure.

I recently heard that FileMaker must process all data from all fields from a table occurrence even if the fields are not being displayed on a layout. Here's what I've heard:

"When FileMaker loads a record, it loads all data from every field of that record (except container fields), even if a field is not on the layout. If you are storing large chunks of text in your database, but hardly ever using that field on a layout, your database may be running slower than it has to. Just move that field to its own table with a one-to-one relationship, and your database will run faster for layouts that do not use that field."

This comes from a very trusted source.

[mr_vodka]

Jon Thatcher discussed this as DevCon 2009. Indeed there are some big benefits to having these 1:1 tables setup.

Accordingly, FileMaker does load all the field data of a record and thus let say you have a huge notes or comments field. It would bring all of it down. Therefore, it is stays in its own table, it will not retrieve it unless it was upon specifically such as when the related field is put on the layout.

I would also assume that this would affect the speed when using indexing on those fields as well.

I would say that for the most part I would keep the fields together as we are used to. However, I have since the summer moved almost all of my comments, notes, and description field which contain large blocks of data into its own table.

[Søren Dyhr]

process all data from all fields from a table occurrence even if the fields are not being displayed on a layout.

I have suspected this for at while... without the quite having the time available to prove it, but it started by to seem so when I was asked to turn something from straightforward reporting into something unscripted because some of the users were on lower versions than fm10.

Ugo's method was thrown into the solution and suddenly we an element of latency observed when 2-3 users attempted to bar-scan to check tickets, in ques for a concert.

To solve the problem did I attempt to break the entire reporting/graphing out in a separate file in a sort of separation model. But what the solution would have benefittet from was if some of the calc'fields weren't bloating the data side of the matter, but for Ugo's model can't you establish the relations with out the bloat, unless a one2one somehow can be established.

It's therefore quite interesting how you have noticed the same, and perhaps could suggest a strategy? I have thouhgt if conditional formatting in some way could be used to pull something like this off?

--sd

[Himself]

So at this moment it would appear that using a 1-1 relationship for bulky data would prove slightly beneficial. That is if the bulky data is not on every form those without it can operate slightly faster.

What about data that is not bulky?

Let’s say the field wasn't a container for attaching other files but rather a number field some number that only applies to 20% of the records so the other 80% get a null value.

What’s the design now?

How about we have several of these kinds of fields non of which apply to the same subset of records so they would all get their own table with its own 1-1 relationship. For example a personnel DB has a "Person" table with the standard ID, First Name, Last Name, MI, DOB etc. but we also want Maiden Name and Spouses Name. Now not every Married person has a Maiden Name (i.e. Men "generally").

So put these fields on the "Person" table or give each their own?

[mr_vodka]

Null/empty values do not take up any space. Therefore having a field only being utilized 20% shouldnt have any impact. Ideally, you probably can keep most of the data together in one table as the impact would be minimal IMHO. However, when dealing with situations such as frequently used tables, putting fields with large data volumes should be put in a separate table where then it could be called upon only when referred to rather than each time you load a record from the orig table.