Re: 2 people with same address - sometimes - standard data model?
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 ThompsonReceived on Thu Jan 12 1995 - 20:35:48 CET