Re: 2 people with same address - sometimes - standard data model?

From: Vincent A Ventrone <vv_at_mbunix>
Date: 12 Jan 1995 19:35:48 GMT
Message-ID: <3f40ak$cda_at_linus.mitre.org>


>i need to track parents of students in order to send them certain mailings
>etc. my plan is to stick them into the same table that i use for student,
> (Stuff deleted)
>my issue is this: some high percentage of these parents live together at
>the same address, but many do not (typically because they are divorced).
>(Stuff deleted)
>oddly enough, none of our current systems seem to have any provision for
>representating the fact that two people have the same address.

We had the same situation in the Data Warehouse we are building. We have a PEOPLE table and a POSTAL_ADDRESSES table. The latter has a generated "dumb" primary key (an INTEGER generated by an Oracle Sequence). POSTAL_ADDRESSES has rows for CITY, COUNTRY, LINE1, etc. Then there is an association table called ADDRESS_USAGE which stores rows that associate PEOPLE rows & POSTAL_ADDRESSES rows (along with some other cols.). The values in the two columns of the ADDRESS_USAGE table that form the association (named PRSN_ID and ADDR_ID) are simply Foreign Keys to the PEOPLE & POSTAL_ADDRESSES tables. Thus, if two (or more) people share the same address, we have a *single* row for the address, but the value of its primary key simply shows up in multiple rows as a foreign key in the ADDRESS_USAGE table. This works nicely for lots of other, similar data: telephone numbers, email addresses, job descriptions, etc.

BTW: our ADDRESS_USAGE table is even a bit more complex than I've indicated, since we also have a USAGE_TYPE col. so that each person can be associated with multiple addresses, each of which is typed (e.g., "home", "work", "vacation", "emergency"). The USAGE_TYPE col. is, again, simply a FK to a TYPES table.

Hope this helps.

-- 
   Vince Ventrone
   The MITRE Corporation       "...In my opinion, there's nothing
   Bedford, MA 01730           in this world beats a '52 Vincent
   vav_at_mitre.org               and a redheaded girl."  -- Richard Thompson
Received on Thu Jan 12 1995 - 20:35:48 CET

Original text of this message