Re: Modeling Address using Relational Theory

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. 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 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.

-- 
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth_at_(Sec)ure(Dat)a(.com)