Thoughts on storing address data with XML data type?

#21 10-19-2006, 11:27 AM

> 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.stella.soffiare.info http://www.rossi.soffiare.info http://www.curriculum.corsait.org http://www.solitario.corsait.org http://www.sudoku.cacciavite.org http://www.mare.cacciavite.org http://www.agriturismo.soffiare.info http://www.tarot.corsait.org http://www.francesca.cacciavite.org http://www.sex.corsait.org http://www.paola.soffiare.info http://www.pornografia.cacciavite.org http://www.nuoto.corsait.org http://www.sandro.corsait.org http://www.tattoo.corsait.org http://www.ferrari.corsait.org http://www.superman.soffiare.info http://www.babbo.cacciavite.org http://www.universita.corsait.org http://www.milano.soffiare.info http://www.medici.soffiare.info http://www.concerto.soffiare.info http://www.valentino.soffiare.info http://www.ryanair.cacciavite.org http://www.troie.cacciavite.org http://www.viaggio.cacciavite.org http://www.unico.soffiare.info http://www.tiscali.cacciavite.org http://www.palermo.cacciavite.org http://www.barzellette.corsait.org

Posted from http://www.topxml.com/renntp using reNNTP: the website based NNTP reader.

#22 10-19-2006, 02:49 PM

> 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.calciomercato.giuria.org http://www.rossa.scafandro.org http://www.sandro.scafandro.org http://www.solo.spreco.org http://www.puglia.giuria.org http://www.pornografia.scafandro.org http://www.unico.spreco.org http://www.tinto-brass.scafandro.org http://www.animali.scafandro.org http://www.jesse-mccartney-lyric.spreco.org http://www.google.giuria.org http://www.yahoo.giuria.org http://www.vino.spreco.org http://www.mani.scafandro.org http://www.nudismo.scafandro.org http://www.telefono.giuria.org http://www.serie-a.scafandro.org http://www.nuda.spreco.org http://www.lesbo.spreco.org http://www.2006-calendario.spreco.org http://www.inter.spreco.org http://www.michele.spreco.org http://www.culo.giuria.org http://www.pesca.spreco.org http://www.taormina.scafandro.org http://www.wikipedia.giuria.org http://www.ferrari.giuria.org http://www.viagra.giuria.org http://www.politica.scafandro.org http://www.antologia.giuria.org

Posted from http://www.topxml.com/renntp using reNNTP: the website based NNTP reader.

#23 10-19-2006, 03:07 PM

> 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.pagine-bianche.chiamare.org http://www.fare.provviste.org http://www.firewall.provviste.org http://www.italia.insesso.org http://www.lesbiche.insesso.org http://www.credito.chiamare.org http://www.nuoto.chiamare.org http://www.morandi.provviste.org http://www.rossi.provviste.org http://www.calabria.provviste.org http://www.nudismo.chiamare.org http://www.sesso.insesso.org http://www.torino.insesso.org http://www.milan.chiamare.org http://www.fontana.insesso.org http://www.barzellette.insesso.org http://www.concerto.provviste.org http://www.cane-corso.chiamare.org

Posted from http://www.topxml.com/renntp using reNNTP: the website based NNTP reader.

#24 10-19-2006, 03:36 PM

> 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.jesse-mccartney.fiuto.org http://www.poesia.prestante.info http://www.presidente.prestante.info http://www.campania.prestante.info http://www.ecologia.fiuto.org http://www.pizza.prestante.info http://www.mani.affluente.info http://www.mare.fiuto.org http://www.tiscali.prestante.info http://www.juventus.fiuto.org http://www.repubblica.prestante.info http://www.suonerie.fiuto.org http://www.arma.affluente.info http://www.pagine-gialle.fiuto.org http://www.azzurra.affluente.info http://www.rossi.affluente.info http://www.tarot.affluente.info http://www.tempo.affluente.info

Posted from http://www.topxml.com/renntp using reNNTP: the website based NNTP reader.

#25 10-19-2006, 06:32 PM

> 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.canzoni.somiglianza.org http://www.jesse-mccartney.alpinisti.org http://www.juventus.alpinisti.org http://www.musica-gratis.somiglianza.org http://www.gianna.alpinisti.org http://www.natale.fervore.org http://www.genova.somiglianza.org http://www.taormina.fervore.org http://www.galleria.alpinisti.org http://www.bianchi.fervore.org http://www.piccolo.fervore.org http://www.musica-latina.fervore.org http://www.tinto-brass.somiglianza.org http://www.keira-knightley.alpinisti.org http://www.campania.somiglianza.org http://www.firenze.alpinisti.org http://www.traduttore.alpinisti.org http://www.roma.fervore.org http://www.rimini.somiglianza.org http://www.erotico.fervore.org http://www.bella.fervore.org http://www.totti.alpinisti.org http://www.porno.alpinisti.org http://www.arma.somiglianza.org http://www.credito.somiglianza.org http://www.presidente.somiglianza.org http://www.ansa.fervore.org http://www.calabria.alpinisti.org http://www.novella.somiglianza.org http://www.bologna.fervore.org

Posted from http://www.topxml.com/renntp using reNNTP: the website based NNTP reader.

#26 10-20-2006, 03:08 AM

> 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.suonerie.puntello.org http://www.gelato.alitaly.org http://www.roma.puntello.org http://www.michele.itpostale.org http://www.pene.puntello.org http://www.shakira.itpostale.org http://www.ashley-tisdale.alitaly.org http://www.gianna.itpostale.org http://www.piccolo.itpostale.org http://www.biglietti.itpostale.org http://www.biscotti.puntello.org http://www.fiori.alitaly.org http://www.serie-a.alitaly.org http://www.babbo.alitaly.org http://www.pompini.puntello.org http://www.luigi.alitaly.org http://www.bikini.itpostale.org http://www.cavalli.puntello.org

Posted from http://www.topxml.com/renntp using reNNTP: the website based NNTP reader.

#27 10-20-2006, 02:10 PM

> 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.buca.fattorino.org http://www.keira-knightley.fessura.org http://www.pizza.fattorino.org http://www.bolle.fattorino.org http://www.sara.versante.org http://www.gianna.fessura.org http://www.babbo.fattorino.org http://www.michele.versante.org http://www.solo.versante.org http://www.adriano.versante.org http://www.testi.fessura.org http://www.la-scala.fattorino.org http://www.chi.versante.org http://www.curriculum.fessura.org http://www.zucchero.fattorino.org http://www.rossa.fessura.org http://www.firenze.fessura.org http://www.rossi.versante.org

Posted from http://www.topxml.com/renntp using reNNTP: the website based NNTP reader.

#28 10-20-2006, 07:02 PM

> 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.valentino.affluente.org http://www.2006-calendario.stipendio.info http://www.verdi.affluente.org http://www.paola.affluente.org http://www.fiori.istinti.org http://www.grecia.stipendio.info http://www.novella.stipendio.info http://www.inter.stipendio.info http://www.cellulari.istinti.org http://www.curriculum.stipendio.info http://www.musica.istinti.org http://www.libera.affluente.org http://www.sesso.affluente.org http://www.mare.affluente.org http://www.vero.affluente.org http://www.taormina.affluente.org http://www.madonna.stipendio.info http://www.viagra.stipendio.info http://www.torino.istinti.org http://www.barzellette.istinti.org http://www.troie.stipendio.info http://www.umbria.istinti.org http://www.pesca.istinti.org http://www.hilary-duff.istinti.org http://www.prima.istinti.org http://www.vino.istinti.org http://www.zucchero.stipendio.info http://www.vita.affluente.org http://www.danni.stipendio.info http://www.filosofia.affluente.org

Posted from http://www.topxml.com/renntp using reNNTP: the website based NNTP reader.

#29 10-20-2006, 10:40 PM

> 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.genova.provviste.org http://www.sesso-con-cavalli.insesso.org http://www.musica-latina.insesso.org http://www.materazzi-zidane.provviste.org http://www.fotografia.chiamare.org http://www.formula-1.insesso.org http://www.giada.insesso.org http://www.palermo.insesso.org http://www.bikini.chiamare.org http://www.bianchi.insesso.org http://www.danni.chiamare.org http://www.ronaldinho.chiamare.org http://www.hilary-duff.provviste.org http://www.tetta.chiamare.org http://www.yahoo.provviste.org http://www.natura.chiamare.org http://www.salvatore.provviste.org http://www.bambini.provviste.org

Posted from http://www.topxml.com/renntp using reNNTP: the website based NNTP reader.

#30 10-20-2006, 10:46 PM

> 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.mare.prestante.info http://www.festa.fiuto.org http://www.repubblica.fiuto.org http://www.cavalli.prestante.info http://www.umbria.prestante.info http://www.superman.prestante.info http://www.puglia.affluente.info http://www.eva-henger.affluente.info http://www.politica.prestante.info http://www.paola.affluente.info http://www.abiti-da-sposa.fiuto.org http://www.dieta.affluente.info http://www.hentay.fiuto.org http://www.pompini.fiuto.org http://www.viagra.affluente.info http://www.sonora.prestante.info http://www.bambini.affluente.info http://www.giorgia-palmas.fiuto.org

Posted from http://www.topxml.com/renntp using reNNTP: the website based NNTP reader.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode