Normalizing table with too many fields

[nexgen]

I have a table which has too many fields which contains various aspects of the product.

Since the database is hosted in filemaker server and accessed via internet, I think it will cause unnecessary load since I think the whole record is downloaded even if I need just a couple of fileds in certain layout.

I think it will be better to split the tables into multiple tables with relevant data. This will cause one to one relationship. Is it good or bad?

If it's good to do that then is there anyway I can automatically create record in other related table whenever the record is added in the original table?

[Wim Decorte]

auto-creation of related records is easy with the 'allow creation' toggle on the relationship.

In general you will get lots of benefits from keeping the table narrow, how much benefit is hard to gauge without knowing more details and the overal design of the solution.

[rwoods]

Hi Nexgen

It sounds like you are not 'normalising' your database, but rather splitting a table in two just to help with a perceived possible performance impact?

The process of normalising is usually performed when you have a number of similar entities that are all related to a single higher-level entity. Instead of having Child1Name, Child2Name, Child3Name for instance you would instead have a table of 'Children' that each relate to a single parent. If each of these children had a large image stored of them, then normalising would allow FileMaker to only download the image of the child you are interested in, rather than having to download all three images if they were all stored in the parent record.

Whether or not just splitting your table in two is a good design decision is debatable, but may create you more work in the future that is not outweighed by any performance benefits. FileMaker is fairly efficient at only downloading what it needs, so you may be fixing a problem that doesn't exist.

[Rick Whitelaw]

Whether hosted or local narrow tables are, imho, the way to go. Much faster for one thing.

Edited February 13, 2017 by Rick Whitelaw

[nexgen]

13 hours ago, rwoods said:

Hi Nexgen

It sounds like you are not 'normalising' your database, but rather splitting a table in two just to help with a perceived possible performance impact?

The process of normalising is usually performed when you have a number of similar entities that are all related to a single higher-level entity. Instead of having Child1Name, Child2Name, Child3Name for instance you would instead have a table of 'Children' that each relate to a single parent. If each of these children had a large image stored of them, then normalising would allow FileMaker to only download the image of the child you are interested in, rather than having to download all three images if they were all stored in the parent record.

Whether or not just splitting your table in two is a good design decision is debatable, but may create you more work in the future that is not outweighed by any performance benefits. FileMaker is fairly efficient at only downloading what it needs, so you may be fixing a problem that doesn't exist.

I had heard that filemaker downloads a single record with all fields from server even if there's only one field in the layout. That means the server needs to calculate all the calculated fields before downloading. Is that correct?

You are right. I'm splitting the table.

[Wim Decorte]

4 hours ago, nexgen said:

 That means the server needs to calculate all the calculated fields before downloading. Is that correct?

 

Not correct.  There are two types of calculated fields: stored and unstored.

Stored calcs get calculated when the record is created or when a dependent field is changed.  They don't get calculated at the moment of download / caching.

Unstored calcs & summary fields get calculated when they need to be used or displayed, basically when their value is referenced.  That does not happen at the moment of download / caching between FM client and FMS.

Having said that, calc fields are very often the root cause of a lot of performance problems in my experience.  They are easy to use but consider them carefully.  Use with caution.

[nexgen]

Thank you for the info.

[rwoods]

Just wanted to agree with Wim here, calculation fields are very tempting, and often very useful, but land you in big trouble on larger solutions, especially when hosted in the cloud across WAN connections.

If you have a customer record and want to display the total value of the sales for that customer, it seems natural to create a calculation field that adds-up the totals from each transaction for that customer. The trouble is that the transaction total is calculated from the total of the line items on that transaction, and the total on each line item is dependant on calculation involving quantity, item value, tax amount etc etc. You can soon get to a situation where you wait several minutes for a list view of all your customers with their total transactions.

The answer in the end can come down to scrapping your calculations, and instead using 'Auto-enter calculations' to have the numbers you need stored, rather than re-calculated each time you need them. The trick then is to make sure you are triggering the update of these stored values whenever they need to change.

There is some good lively banter on this subject at https://community.filemaker.com/ideas/1489