01-19-2007, 05:35 PM
|
| |
 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 http://www.tonemr.org/radio http://www.xzzurri79.org/alessandro-del-piero http://www.tonemr.org/lettori-mp3 http://www.cove2r35.org/fotocamere http://www.xzzurri79.org/italy http://www.cove2r35.org/musica http://www.xzzurri79.org/farsi http://www.tonemr.org/vacanze http://www.tonemr.org/salvatore http://www.xzzurri79.org/cristiana http://www.cove2r35.org/free http://www.xzzurri79.org/palermo http://www.cove2r35.org/bisex http://www.xzzurri79.org/google http://www.xzzurri79.org/love http://www.xzzurri79.org/canzoni http://www.xzzurri79.org/html http://www.cove2r35.org/telefono http://www.tonemr.org/forza-italia http://www.xzzurri79.org/calciomercato http://www.cove2r35.org/solo http://www.xzzurri79.org/claudio http://www.xzzurri79.org/poesia http://www.tonemr.org/serramenti http://www.xzzurri79.org/video http://www.tonemr.org/primo http://www.cove2r35.org/stampanti http://www.tonemr.org/angelo http://www.xzzurri79.org/umbria http://www.xzzurri79.org/zidane http://www.cove2r35.org/cartucce http://www.tonemr.org/nati http://www.cove2r35.org/bolle http://www.tonemr.org/nick http://www.xzzurri79.org/pagine-bianche http://www.tonemr.org/pool http://www.tonemr.org/elettronica http://www.tonemr.org/universita http://www.xzzurri79.org/web-site http://www.tonemr.org/search http://www.cove2r35.org/dei http://www.xzzurri79.org/garibaldi http://www.tonemr.org/news http://www.tonemr.org/lavoro http://www.xzzurri79.org/casa http://www.cove2r35.org/girl http://www.tonemr.org/dati http://www.tonemr.org/mms http://www.tonemr.org/eva-henger http://www.xzzurri79.org/programmi http://www.xzzurri79.org/liberi http://www.cove2r35.org/hentay http://www.cove2r35.org/suzuki http://www.tonemr.org/michele http://www.xzzurri79.org/sesso http://www.cove2r35.org/informatica http://www.tonemr.org/photo http://www.tonemr.org/erotismo http://www.xzzurri79.org/venezia http://www.xzzurri79.org/dizionario http://www.cove2r35.org/progettazione http://www.xzzurri79.org/mario http://www.tonemr.org/autoradio http://www.tonemr.org/piscina http://www.cove2r35.org/la-scala http://www.tonemr.org/winmx http://www.cove2r35.org/vasco-rossi http://www.cove2r35.org/positano http://www.tonemr.org/sex http://www.cove2r35.org/keira-knightley http://www.tonemr.org/acqua http://www.xzzurri79.org/sandro http://www.xzzurri79.org/virgilio http://www.cove2r35.org/iran http://www.tonemr.org/giada http://www.cove2r35.org/email http://www.cove2r35.org/shakira http://www.cove2r35.org/taormina http://www.cove2r35.org/buffon http://www.cove2r35.org/pompei http://www.cove2r35.org/sports http://www.cove2r35.org/abiti-da-sposa http://www.xzzurri79.org/ferrari http://www.cove2r35.org/tiro http://www.xzzurri79.org/pornografia http://www.cove2r35.org/cosi http://www.tonemr.org/xxx-gratis http://www.cove2r35.org/mondo http://www.tonemr.org/dal http://www.xzzurri79.org/loghi
BizTalk Utilities - Frustration free BizTalk Adapters http://www.topxml.com/biztalkutilities  |
Linear Mode
