Best practice for storing contact information (phone, e-mail, address)

Lounge (Discussions)
1

Hello,

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?

Regards,

Matteo

2

Why not a separate table for phone numbers and also for addresses?

A person can have zero, one, or more phone numbers.
A person can have one or more addresses (home, business, ...).

I wouldn't design my database from Excel's point of view. There are ways around that.

2 Likes
3

I am also for separate tables.

Also we keep old addresses and phone numbers and mark them outdated.

3 Likes
4

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.

5

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.

2 Likes
6

I use this technique all the time, it's very useful. Beware though: Confusing 1 to Many Find behavior with relationship sorted by Date - #3 by bdbd

7

If you do the multi-table approach, you can set up relationships using calculated fields for various types, e.g.

Phone_Home relationship:  
   Person::ID = Phone::fkPersonID and 
   Person::kHome = Phome::Kind

   sorted by Phone::EditTimestamp reversed
 
where 
Person::kHome is a calc field which has the value "home"

You can then create "shadow" fields in the Person table to make exporting easier:

Person::PhoneHome = Phone_Home::num
Person::PhoneWork = Phone_Work::num
Person::PhoneMobile = Phone_Mobile::num

Or even a "smart" field which picks the best one based on what data exists...

// prefers the mobile phone number
// but gives the home or work phone # if blank
Person::PhoneBest = Case(
  PhoneMobile ≠""; PhoneMobile;
  PhoneHome ≠""; PhoneHome; 
  PhoneWork
)
1 Like
8

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.

9

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.

10

I normally use one table for all contact methods. The default phone number and address are additionally stored in the contact table. For me that fits my needs best.

11

This is where selective denormalization can come into play, and with it, selective duplication of data.

2 Likes
12

You can also go to the native table an list the data in that table for a given company (whatever). If the company-ID ist stored in the people table, fast.

13

This thread/discussion is kind of funny to me.

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.

1 Like
14

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.

3 Likes
15

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.

1 Like