Proposed v2.1 Schema Change to Address table

Published
2008-04-07 04:51
Written by
Continuing the database cleanup that we started with v2.0, we are planning to drop some of the unused fields from civicrm_address table. CiviCRM v2.0 ERD List of fields that will be dropped. 1. street_number 2. street_number_suffix 3. street_number_predirectional 4. street_name 5. street_type 6. street_number_postdirectional 7. street_unit 8. supplemental_address_3 These fields were originally included with the intent that they would be useful for walk-lists or other use cases where address segments were needed. However, as far as we know - no one has taken advantage of these pieces of data. Hence we are planning to drop it in v2.1. Currently you cannot add/update these fields through UI. So if anyone wants these fields please let us know asap.

Comments

Anonymous (not verified)
2008-04-07 - 11:19

Hi Kurund,

I'm pretty interested in using these fields, but unfortunately can not provide the resources to sponsor such project.

(cf. http://forum.civicrm.org/index.php/topic,2352.msg9987.html#msg9987 )

Furthermore I think it makes sense to support these fields - at least from a "German perspective": As far as I see, it is common to seperate the fields (especially street name, number and suffix) for city administration and other govermental offices all over Germany, but also for address broker, direct mailing lettershops etc..

Please confirm e.g. the schema

- of the ministry of internal affairs of the german state Mecklenburg-Vorpommern
http://www.laiv-mv.de/land-mv/LAiV_prod/LAiV/AfGVK/_liegenschaftskataster/formatbeschreibung_HK.pdf

- or of AZ Direct, the Bertelsmann media company address broker:
http://shop.az-direct.com/azonline/pdf/Satzaufbau%20AZ%20Privatadressen.pdf

If you need help with reading / translating the files, I'll happy to assist.

Hi Kurund,

From a Dutch perspective it is also logical to separate the streetname and housenumber. This is done to keep addresses consistent in the database. Large Dutch databases check their addresses against a postal code database which is supplied by the postal service. Typing in a postalcode will lead to a streetname and range of house numbers. I have seen the first online versions of this appearing.

In my work experience I have always seen that larger databases wish to separate the street name, house number and suffix at some point in able to have more control of the addresses and achieve better dataquality. So in the Netherlands it is considered 'good practice' to do so.

Anonymous (not verified)
2008-04-08 - 01:50

Hi friends. ¿There're any roadmap for version 2.1? We're a little ONGD using Drupal 6.1 and are very interesting in this module.
Thanks in advance

In countries that do not have a post code (there are a few, such as the Republic of Ireland) and which lack as 'formal' an address structure as say the Netherlands or the US, there is often a need to have an additional supplemental address line to capture granular townland/locality information.

If you are proposing changes to the address structures I'd heartly recommend you take a look at the work of Graham Rhind in the Netherlands who is an expert on postal address data quality and address structures world wide and is also a consultant on web usability and 'cultural' issues in websites. His stuff is really good. His website is http://www.grcdi.nl/

Often breaking out addresses into standardised 'atomic' elements makes it much easier to put them back together in formats that are appropriate to national postal services and 'cultural' norms in a country - the key is to have appropriate mappings from the physical data structures to the 'real world' entity so that you can reproduce the address in the correct format for the country. In that context I'd caution against removing any elements, unless you are going to create a 'generic' address format that will work globally in an intelligent and intelligble manner.

Please do NOT make the mistake that 99% of non-OpenSource vendors do of assuming that all addresses conform to a US address structure (or similar) and that everyone has a postcode. This leads to poor quality information in the database as people try to shoehorn in data into fields it doesn't belong in. I'm seeing this with a CRM database I'm looking to migrate to civicrm at the moment where non-US data has been buggered around with to make it fit the 'off the shelf' structure. It is not pretty.

For other discussions of information quality issues in general, check out www.iaidq.org (International Association for Information & Data Quality) - an organisation that is currently evaluating CiviCRM to use for international memberships.

A more valuable approach might be to provide mappings for country address structures to a CIVICRM address template... I'd be willing to contribute and assist with that mapping work (essentially a doc resource) - partly because I've done similar projects before. I may be able to get support from IAIDQ members world wide on this as well - I'll look into that if you want.

I think we should keep these fields. The failure is not with applications that would like to use them but with CiviCRM not parsing address data into them. These fields would be extremely useful for building walk lists.

To the extent that these fields are required to help do walklists, I definitely think it would be good to keep them - as we have clients who will want this feature.

Canadian federal and provincial voters all provide addresses broken out into these fields. I would like to see them kept.

Anonymous (not verified)
2009-06-23 - 15:11

I agree that it's odd that these fields exist but we can't access them via the UI.

However, rather than delete the fields, I'd much rather see the reverse, i.e. the UI modified so that we can use them after all! This is the one feature I really need for UK addresses - as others have said, different countries do things differently!

On another matter, and I'm probably asking this in the wrong place as it relates to CiviMAIL, I'd really like to be able to assign "from:" email addresses on a user by user basis. Any ideas where I should look or where to post this question?

Thanks for any help and guidance - I'm new here!

Pete