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

My apologies for not sorting out who said what. :)

>
> 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).
>
> for many reasons, it pains me to store *two* addresses when the addresses
> for two people are often the same.
>
> i suppose i could have some sort of "household" table that is referenced
> by the person table, but that seems sort of clumsy. one problem is that i
> can't think of an obvious external key to let my users jump between the
> two tables. i can't have my screens hide the fact that the addresses for
> two people are the same, since i need some way to *change* whether they
> are the same or not.
>

The clincher here is that you couldn't think of a key for "household". This makes you wonder if "household" has *meaning* in your data model. Students don't have a relationship with their parents via the household: the relationship is direct. A couple of examples would highlight this.

If a student's parents divorced while the student is in your database, you would have to create a new household, then "transfer" one of the parents. Seems unnatural. The data entry needed doesn't map to anything that really happened that you care about.

If a divorced parent remarried, you would have to arbitrarily decide whether to include the new step-parent in the household. If you have siblings in your database, one may want to include a step-parent in his mailings, the other may not. This can't naturally be modelled with the household concept. Step-siblings confuse the household concept even further. In other words, you can't assume a step-parent gets mailings just because (s)he is in a household. You need to model the relationship between a student and a (parent? guardian? relative? interested party? co-signer?) directly.

The point here is that many if not most questions of data modelling must be addressed by examining the *meaning* of the data. Only after you have dealt with meaning can you afford the luxury of trying to compress the data into a smaller package.

btw: You should probably deal with duplicate addresses in the application logic, with a "same address" or "copy address" function or by defaulting in the previously entered address, or some such approach. Got to keep those data entry folks happy! :-)

• Nat Brooks nbrooks_at_bbn.com Project Leader Bolt Beranek and Newman Inc.