CiviCRM 2.0 Design Series - Simplifying Data Storage and Retrieval for Address, Email and Phone Data

One of our primary objects for the 2.0 release is to find ways to simplify the way data is stored and retrieved in order to make querying data from the DB easier and more scalable/efficient. We're questioning a number of different pieces of the current data model - and one big area is storage of address, email and phone data. Part of this process also involves identifying areas where the data model is "out of touch with reality" (i.e. it imposes unrealistic or artificial constraints on the data). Our current approach for handling address, email and phone info is to "group" these fields into Locations - each of which is assigned a location type (Home, Work, etc.) . Locations can belong to contacts (people, organizations, households) - but they can also belong to Events and to the organization which owns a CiviCRM installation (the "Domain"). Each contact can have one-or-many locations - and each location has an address and one-or-many phones and emails. This approach is quite flexible - and has worked reasonably well for most CiviCRM users as far as we know. However, it has several significant drawbacks:

• Forced grouping of email, phone etc. with a specific physical address does not reflect how many people look at this data. A person's mobile phone is not really a "Home" phone OR a "Work" phone.
• The current model forces the primary phone, email etc. to be linked to a primary "location". This also often doesn't match reality.
• Querying address, phone, email etc requires an extra level of joins and is less efficient.

The proposed approach links addresses, phones etc directly to contacts - which represent 95%+ of the actual data. A join table will still be used for any other objects which require address, phone, email data - e.g. Events, Domains... Each address, phone, etc carries it's own location type, along with an optional descriptive name. The primary address, phone, email address for each contact are independent of location "type" - so one contact might have "Home Address" as primary physical address, and "Work Phone" as primary phone. Details on the current and proposed schema can be found on the wiki. I think we should also re-evaluate the layout of the "location" part of the contact input forms. The current layout groups address, email(s) and phone number(s) into location "blocks". For example, a block for "Home" info, a block for "Work" info etc. However, phone numbers (especially mobile phones) and email addresses are not necessarily associated with a particular physical address. It may make more sense to just provide sections for:

• One or more addresses
• One or phone numbers
• One or more email addresses

... and a checkbox to mark one of each as "primary". We'd like to get feedback from folks about these changes. Do they make sense? What changes do you think are needed in the input and display of this contact data? If you're a developer, do you think the simplified data structure will help you? You can comment here, on the wiki page or post a thread on the forums.

Comments

One issue with using Organisations at the moment is that the contact details for the organisation do not flow through to its employees.

For example to avoid having to change all the work addresses of a whole bunch of employees when their employer moves location, it would be preferable if the employing organisation's location at which they work is visible within their profile and can be set as the preferred location for correspondence.

By inheriting the location from the employer, changing the employing organisation's location address could then flow on to the employees for the purposes of future mailings (including printing mailing labels).

A similar thing could be said for households

It would be great if this could get addressed in this review.

Thanks for the great work! We have done some work on CiviMail that we'd like to contribute to the next release.

hey andrew:

We have addressed the householding part of this (in a slightly hackish, but workable way) for 1.8. When creating an individual, you get an option to use the "shared household" address. (we clone the address and keep it in sync as needed). I suspect doing the same for employer is not too hard.

However, the cloning part is not great. We however could not think of an easy solution to get search to work seamlessly with shared addresses. But this is a good reminder for us to put some more thought into this