Re: Modeling Address using Relational Theory

Kenneth Downs wrote:
> dawn wrote:
>
> > I've looked at a bunch of address models and am wondering if there is a
> > generally-agreed-upon relational model for the parts of the address
> > that are not city, state/province, country, or postcode.
> >
> > There is a standard multivalue model for it that is exceedingly simple:
> > Name of attribute: AddressLine
> > Display size: 30 chars (or so)
> > Cardinality: multivalued, likely with constaints in the code (I know, I
> > know) to limit this to 3 lines
> >
> > What I keep seeing in self-described relational models is Line1, Line2,
> > Line3. That just seems wrong. What would be the purest relational
> > approach to this? What percentage of the time is it used when the
> > implementation is an RDBMS (or SQL-DBMS)?
> >
> > Thanks. --dawn
>
> well if I were trying to keep accurate records, I would want to know what I
> was supposed to keep track of. Whether it was on stone tables or
> relational tables would be secondary.
>
> The first thing you find is that addresses are formed in different ways in
> different countries, so lets limit for the moment the discussion to the US.
>
> It's getting on 4 years since I did CASS-certification, but as I recall the
> US Postal Service breaks information up quite precisely.

Yes, but few companies see a benefit in modeling the data that way since it can be parsed for such if required (and it rarely is required other than for de-duping).

> There is a house
> number, with possible sub fields, a street name, which might be a rural
> route, an apartment or box number, a city, state, and the zip code, which
> has subfields of 5 chars, 4 chars, and (AFAICR) 3 more chars.
>
> So the correct table design

when you say that, smile ;-)

> would contain columns of the proper length to
> keep this information.
>
> Ironically, however, if you send this information through commercial address
> processing software, they are going to parse it for you anyway, so you can
> actually store Address 1, Address 2, City State and Zip.

I don't see the irony. What I see is that there are times when it might make sense to haul out some operators on the "addressLine" domain to parse it out, but there is little need to model these address lines with pieces split out. Custom routines have been and can be written for those few instances where this is important, compared to the routines that otherwise need to be written and executed frequently for pasting address lines together.

Have you ever seen an implementation of "the correct table design" (or even close) for a relational model of address data as you see it? --dawn

> --
> Kenneth Downs
> Secure Data Software, Inc.
> (Ken)nneth_at_(Sec)ure(Dat)a(.com)
Received on Mon Aug 29 2005 - 02:47:58 CEST