Normalisation

This is a very unscientific reply to the problem.
It is not the number of fields that determines normalisation, it is the relationship between the fields. You can have +100 fields in one table and be perfectly normalised.
But that's another topic...

4 Likes

Sure, it's possible, but I think my rule of thumb is not unreasonble.

As an example, consider a "Contacts" table with these fields

HomePhone
HomeEmail
HomeAddressStreet
HomeAddressUnit
HomeAddressCity
HomeAddressState
HomeAddressZip

Many people would start with a table like this, and then add more fields:

WorkEmail
WorkPhone
...

And then perhaps later

SecondaryWorksiteEmail
SecondaryWorksitePhone
...

and so on, quickly hitting 50 or 100 fields.

The normalized version of this would have separate Email, Phone, Address tables, each with a "type" or "kind" field (work, home...)

I'm curious, do you have a table with 100+ fields that is fully normalized? I'd be interested in seeing the field names...

Not so clear cut. Denormalization or selective denormalization is an important step after normalization.

5 Likes

Unfortunately, this is especially true in FileMaker because (a) table joins can be extremely slow, and (b) calculated fields are limited across relationships (they can't be stored or indexed).

1 Like

well...
a 'normal, quite small' sql solution will easily have 100 primary tables, each of them with a handfull fields

a FileMaker solution on the other hand often has just a few tables but manymany fields

I'm very happy when I been called to help a customer (we do support 'foreign solutions', not made by our company..) and find less than a couple of 100 fields per table...
This is not good (to have that much fields in one table)! But one can also normalize a db-app to death...

The ease and the speed of 'developing'with FileMaker has its draw-backs! But understanding a highly normalized sql-solution is quite some effort...

Somewhat off-topic:
We are in environments where customers must have some certification (iso 9k, iso 27001, etc.) where the requirements for developing/maintaining a solution has become strict. Therefore, also the developing-process must be similar (if not the same) as with 'sql development' - having thousends of fields in one table will be problematic (I got a solution from a customer with ~5000 fields...)

1 Like

In that case you are right and I also use a separate table "Comm" for this purpose.
But for example a large project can have so many properties that have nothing in common like communication data

A part of the complete list (sorry in Dutch):

I don't read Dutch, but my guess is that these fields:
image
properly should be located in a separate Contacts table.

As structured, you can can only have a single eKlantContact per Contract, whereas if it were better normalized, you could have any number.

(I realize this thread is way off topic now, my fault, I can post another thread to talk about normalization if we want to keep going on this topic)

That's right but pure normalisation is not always the best choice.
These fields are even not necessary, they also belong to the contact table, but, you know that searches in Filemaker in related tabels can taken much more time than indexed fields.
The fields in the example are just to perform super fast searches in tables with >100.000 records. You can't do that with perfectly normalised data, it just takes to much time. Users don't want to wait 3 sec for a result. Filemaker has its drawbacks too. Experience will learn you what you can do and what you have to do using tricks.