I am currently thinking about the way I store people's contact information in my databases.
Normalization leads to storing this information in a separate table, but is this always the most practical way?
I could think of the following ways to do this:
Use several fields in the "people" table
Using this approach I would create "phone_landline_personal", "phone_mobile_personal", "phone_landline_business" and "phone_mobile_business". This should cover most use cases and it is quite simple.
Use a related table
Here I could specify the type of the phone number and if it is a personal or a business number. Additionally I could mark one of them as "prefered number". I could even mark some numbers as "do not share".
I really like the second approach but if users want to export information regarding people into Excel it gets a bit more difficult as I could export only the prefered number.
A possible solution could be to use a script to store all those numbers (or those that may be shared) into a text field in the people table and export this field as well.
(of course the same applies to e-mail addresses and mailing addresses)
Any thoughts on this? Are there best practices? How do you do this?
One simple trick for keeping a history of address changes is to use a Reverse Sort in the relationship. Sort on a serial number field or a field that stores Get(RecordId). This will show the most recent record first.
I'm a fan of the separate table too. Maybe even a step further by supertyping phones/emails/slack/carrier pigeon etc as ContactMethods. Then you can list all ContactMethods in a single portal if desired without needing dedicated fields/tables for each type.
I like the idea of modeling the data based on the real world, where someone could reasonably have many of each type.
But if adding dedicated fields directly to the contact record is appropriate for the current requirements, I also wouldn't hesitate to just do that and refactor later if it ever becomes an issue. No need to prematurely normalize data. Separate table(s) are usually more work in the short and mid terms.
I also consider separate tables for each address or contact element, with corresponding join tables, is best. Many contacts have multiples of each of these, and some may be shared by more than one contact. For example, you might have a contact record for a company and also for one or more staff member in that company; you might have a couple each of whom shares address, phone number, etc.
Separate table is from the database design perspective better solution. But there is one drawback in it: if you want to have a list of people and show their contact details there, list will be quite slow. Related fields are making lists quite much more less usable than fields from the same table. In FileMaker there is always good to think how you want to show data and make database design decisions based also on that.
Last week I had a starter webinar for MongoDB and well modelling seems to follow a different approach there with many steps of data normalisation seen as unnecessary.
Their thinking goes more in storing objects with dependent properties in one document with document being the equivalent of a row in SQL DBs. The introduction reads like "Data That Is Accessed Together Gets Stored Together".
That made me think of using the "Execute FileMaker Data API" script step to fill a MongoDB with data and getting e.g. an automated parallel structure/backup from live systems. Also seems to hint at a possible way to migrate to the coming engine.
Yes, in MongoDB you optimise for reading, not writing.
You store the addresses with the customer, because that is just one or two records usually.
So when your web app retrieves the user record, it doesn't need a second query to address table and make a join.
Now when you update address, because a company or family moved, you have to edit many records as the data is stored redundantly.
So slower write on change, but faster read.
You can do the same in FileMaker if you like, e.g. store addresses as JSON array in a field with the person record.
This is very interesting. MongoDB is very different compared to FileMaker. It's very exciting to follow the development of Claris Studio that encapsulates MongoDB - is that the good word ? - and lets work the developer without needing to know the ins and outs of it.