Re: Modeling Address using Relational Theory

Mike wrote:
> On 9 Sep 2005 05:20:41 -0700, "dawn" <dawnwolthuis_at_gmail.com> wrote:
>
> >I'd rather see what your conceptual model is.
>
> In my experience, people don't learn much if they don't do their own
> thinking.

When students ask questions in this newgroup, then the Socratic method might be appropriate. I doubt there are many who read this group that aren't really clear that I do my own thinking (for better or worse), so I appreciated Marshall challenging you on this.

> >I still think that the address I'm trying to model is an id for a
> >location.
>
> In my own work, I sometimes come across addresses like this
> fictionalized one.
>
> 123 Flintstone St
> Mountain Pass, CA 12345
>
> This mailbox isn't in Mountain Pass, Calif. (Neither is the building
> we might associate with it, but that's a slightly different issue.)
> It's in a city on the far side of the ridge.

But the entity as a whole is an identifier for a location to which the postal service would deliver an item with such an address, right?

> I also have addresses like this fictionalized one.
>
> RR 2 BOX 1999
> BEAVER FALLS ID 54321
>
> The mailbox (and the building we might associate with it) aren't in
> Idaho. The post office that handles its mail is in Idaho; the mailbox
> (and the building) are on the Wyoming side of the Idaho-Wyoming border.

And the address as a whole is an identifier for a location to which the postal service will deliver an item.

> Mail sent to the fictional ship Wibble might be addressed like this.
>
> USS Wibble
> FPO AE 99999
>
> "USS Wibble" is neither a street address nor a post office box. "FPO"
> is not a city. "AE" is neither a state nor a possession. The Wibble
> itself is certainly not "in" AE, which refers to Europe, the Middle
> East, Africa, and Canada. It's probably in the water, and today it
> could be in the Pacific Ocean.

Good example. And again, the address is an identifier for a location to which the postal service will deliver an item.

> If you want to develop a conceptual model, you're probably better off
> starting with representative sample data and thinking about it, rather
> than starting with assertions or hypotheses about what addresses
> identify.

When I started modeling address entities in the 1970's, I know I had a lot of examples and I have continued to collect them over the years. I didn't have a Ship address in my current collection, however, so I'll defintitely tuck that one away -- thanks.

If modeling an address, before you start piecing it apart, you should figure out what your entity is. In the case of a complete address, I am modeling a location to which a physical item could be sent. You have pointed out that when you piece out the properties of this address, you need to understand the nuances of what the related values mean.

If the address for a person is in Idaho and the person is in an office in Wyoming, you would need to decide whether it is important to also model where the person's body is located, or where their office chair is located. Rarely is that cost-justifiable. But you are right to point out that there are a lot of nuances, which is why it is important to know what it is you are modeling. When modeling "city" as a property of an address, you are not modeling city as a direct property of a person. That is why even folks like me who feel free to model complex text-based data types as properties of entities (rather than as separate weak entities), typically model an address as a separate entity.

> You can get a lot of good sample data *for the US* from USPS Pub 28.
> Don't expect other postal authorities to do things the way the USPS
> does.

I had not kept current on (or retained) international address standards, so I was surprised to read that US domestic addresses are not a subset of international address (but with the country assumed). The US domestic address puts the APT # in different places than an international address sent to the US (but we can put a man on the moon). cheers! --dawn

> --
> Mike Sherrill
Received on Tue Sep 13 2005 - 23:57:52 CEST