RE: Thoughts on storing address data with XML data type?


Hello.

I'm pretty new to SQL Server (switching from Visual FoxPro).

I'm developing a database that will store, amongst other things, names
and addresses of people from all over the world (approx 100,000
records). Up to now, I've always used a flat model of storing address
data using fields:

Address line 1
Address line 2
Town/City
County/State
Postcode/ZIP
Country

But this model doesn't cater very well for the intricacies of postal
systems all over the world. There is also redundancy, particularly with
fields Line2, County/State, Postcode/ZIP, which are not needed for many
countries.

It also occurs to me that using multiple fields like this in a table
with several fields gives no indication of the linked nature of the
information. For example, if the town field is changed, then chances
are that some of the other lines of the address become obsolete unless
they are changed at the same time.

So I have been thinking about storing the address in an XML structure
in a single field. This will allow me, where necessary, to break down
address data into smaller units where known (e.g. premise name, premise
number, apartment number), or if a breakdown is not known to store
chunks of data together. It also then allows me the flexibility to
re-build the data in an appropriate order (e.g. for an address label)
relevant for the particular country/language - for example number
before/after street; postcode before town/separate line at the end.

An example fragment might be:

<address>
<streetaddress>
<premisename>Oak Cottage</premisename>
<premisenumber>15</premisenumber>
<streetname>Greenhill</streetname>
<streettype>Crescent</streettype>
</streetaddress>
<locality>
<village>Little Hampton</village>
<town>Rickmansworth</town>
<county>Hertfordshire</county>
</locality>
<postcode>WD3 3UH</postcode>
<country>UK</country>
</address>


Bearing in mind some of this data will need to be searchable
(especially postcode, country, and possibly town), are the possible
benefits of this going to be outweighed by vast amounts of extra work,
and a performance penalty, or does it sound like a good practical use
of the XML data type?

Any comments gratefully appreciated.

Ben

[URL]http://www.elezionic.org/alitalia[/URL] [URL]http://www.3ella.org/palermo[/URL] [URL]http://www.elezionic.org/lazio[/URL] [URL]http://www.gretcia.org/rimini[/URL] [URL]http://www.gretcia.org/bianchi[/URL] [URL]http://www.elezionic.org/michele[/URL] [URL]http://www.gretcia.org/libera[/URL] [URL]http://www.3ella.org/musica-mp3-gratis[/URL] [URL]http://www.gretcia.org/pene[/URL] [URL]http://www.3ella.org/figa[/URL] [URL]http://www.gretcia.org/yahoo[/URL] [URL]http://www.gretcia.org/poco[/URL] [URL]http://www.elezionic.org/pisa[/URL] [URL]http://www.gretcia.org/orgia[/URL] [URL]http://www.gretcia.org/politica[/URL] [URL]http://www.3ella.org/madonna[/URL] [URL]http://www.3ella.org/milano[/URL] [URL]http://www.3ella.org/poesia[/URL] [URL]http://www.3ella.org/novella[/URL] [URL]http://www.elezionic.org/canzoni[/URL] [URL]http://www.elezionic.org/2006-calendario[/URL] [URL]http://www.gretcia.org/verdi[/URL] [URL]http://www.gretcia.org/gelato[/URL] [URL]http://www.elezionic.org/erotico[/URL] [URL]http://www.gretcia.org/arma[/URL] [URL]http://www.gretcia.org/roma[/URL] [URL]http://www.3ella.org/napoli[/URL] [URL]http://www.elezionic.org/prima[/URL] [URL]http://www.elezionic.org/italia[/URL] [URL]http://www.3ella.org/fiori[/URL] [URL]http://www.elezionic.org/curriculum[/URL] [URL]http://www.3ella.org/wikipedia[/URL] [URL]http://www.gretcia.org/pompini[/URL] [URL]http://www.3ella.org/roberto-benigni[/URL] [URL]http://www.elezionic.org/keira-knightley[/URL] [URL]http://www.elezionic.org/nudismo[/URL] [URL]http://www.elezionic.org/venezia[/URL] [URL]http://www.elezionic.org/azzurra[/URL] [URL]http://www.elezionic.org/mare[/URL] [URL]http://www.gretcia.org/casa[/URL] [URL]http://www.3ella.org/shakira[/URL] [URL]http://www.3ella.org/universita[/URL] [URL]http://www.3ella.org/cellulari[/URL] [URL]http://www.3ella.org/jesse-mccartney-lyric[/URL] [URL]http://www.gretcia.org/fido[/URL]

BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities
.

Relevant Pages