Redesigning the Location Data Model

I assume the two-entry limit is for non-contact addresses, right? I'm polling 17 email accounts and I have seven accounts in my IM client...

I'm wondering whether the civicrm_contact table couldn't be extended with an primary_email, primary_phone, etc. columns instead of the is_primiary ones; we could get rid of the 'only one per contact can be primary' logic.

+1 Multiple locations just adds rarely needed complexity for mid level users.

"The proposed approach links addresses, phones etc directly to contacts - which represent 95%+ of the actual data."

That matches our experience.

Neil 

I've been thinking about this a bit

I think it might be easier to keep the is_primary and is_billing field in the tables (since we can potentially use it in other scenarios too) and then use a "Primary Contact View" and "Billing Contact View" which basically gives us a virtual: Contact + Address + Phone + EMail table for both cases.

I dont think we are saving in logic / space in either case. We'll still require a join and still need to keep track of which address / email / phone is primary for a contact

What are civicrm_address.name, civicrm_email.name, civicrm_phone.name ???  I might call my phone names sometimes, but it doesn't need to be tracked. 

Why are there two name fields in civicrm_im? 

I'm also not quite sure that I understanding the need for civicrm_location_block.  Can't Event Location and Domain Location just be linked to contacts?  (I've never used CiviEvent so forgive my ignorance). 

I have to say that the prospect of a denormalised data model is a big turn off. If MySQL 5 is required, couldn't some of the "extra" joins be put into SQL views? If updating or deleting rows is a problem and you need to deal with the SQL view's results, just use triggers to modify the data in underlying tables.

Having two apparently identical and redundant foreign keys in the loc_block table doubles the indexing required. An index in MySQL is made for each field when they're foreign keys. Putting location_type_id in every contact table adds many additional indexes on the existing model. I can't speak to the performance cost of this, but indexes can be expensive. Often they want to be cached in memory.

I agree that breaking out the phone/email fields so they're not tied to a specific address has value. Perhaps expand the ability to classify the type of phone (currently just phone/fax/mobile/pager, as I recall) and email, and give admin ability to add/modify that dropdown list. That should provide the necessary classification flexibility. One problem we find with the current structure is that when someone makes a purchase, their email is tracked under the billing address, which means you're often tracking the same email in two places (since billing address is rarely the primary address). Since billing email and address are a special case, maybe there should be a way to multi-classify an address? For example, mark a single address as home and billing?

The current flexibility in having multiple addresses, flagging them by type, and having a text field to add custom names, is invaluable. It's flexibility and functionality beyond many other systems we've used, and I would not want to lose that. The method of flagging one address as primary is also very well constructed. The proposed schema changes seem to make sense. Seems like it will simplify querying to have is_primary part of the actual address table/record to which it applies, without any harm to data integrity/normalization.