On Sun, Aug 10, 2003 at 06:00:39PM -0400, ethan@ethanzimmerman.com wrote:
> I'm helping my dad with a database he's putting together for work and
> I'm trying
> to figure out how to get around this problem:
>
> - he's got a person table and an apartment table (it's a retirment home)
>
> - To cut to the chase I'm trying to find an elegant way to pair up
> two married
> people from the people table (i.e. Tom Jones and Nora Jones are a married
> couple
> and each exist as a seperate record in the people table) So I can then
> refer to
> the couple as a single entity
>
> - some ideas
> - I could give each person a marrige ID number and then pair up the
> numbers
> - I could use a Spouse ID field that links to the appropriate record
> in the
> person table (but that makes the pairing subroutine a bit harder)
>
>
> Any ideas about how I should do this?
I'm not clear why you need to treat the married people as a single
entity. You could simply have a field in the people table that indicates
what apartment they're in. This way, you could have as many people as
you like sharing an apartment. The reference would be from the people
table to the apartment table.
If you truly need to refer to the couple as a single entity, then I
suppose a couple table is the best way. You'd have an entry for each
person in the table, that referred back to the people table. But it
brings up two problems. First, if you're going to use the couple_num
field in the apartment table, then you're going to have to have both a
person_num and a couple_num field in the apartment table. This will make
queries needlessly complicated (let's see, is there a couple or a single
person in the apartment?). Second problem is the possibility that more
than two people could share a room, or that two unmarried people could
share a room (like elderly sisters). If that's even a remote
possibility, I'd suggest the scheme I mentioned in the first paragraph.
Paul
-----------------------------------------------------------------------
This list is provided as an unmoderated internet service by Networked
Knowledge Systems (NKS). Views and opinions expressed in messages
posted are those of the author and do not necessarily reflect the
official policy or position of NKS or any of its employees.
This archive was generated by hypermail 2.1.3 : Fri Aug 01 2014 - 16:28:02 EDT