I would expand this concept into inter-person relationships, so that you could
document all kinds of relations. You could also set up a table called
'relationship' that looks something like this:
create table relationship (
relationship_id numeric(9) autoincrement,
person_id numeric(9),
other_person_id numeric(9),
type char(1)
);
The assuming Tom is person 1001, and Nora is 1005, you could represent their
relationships like this:
insert into relationship (person_id, other_person_id, type) values
( 1001, 1005, 'W' ); // 'W' for wife
insert into relationship (person_id, other_person_id, type) values
( 1005, 1001, 'H' ); // 'H' for husband
Then you join the person table against itself, using the relationship table to
identify their partners (of course, use "WHERE TYPE IN ('H', 'W')" to catch
both husbands and wives).
Marriage is, of course, a reciprocal relationship, so two records are required
to show this, unless you think 'S' for spouse is sufficient for your needs.
This design could allow multiple numbers and types of relationships. Some
relationships are not reciprocal, such as an heir, and most often, the role
of an heir is a second role for that person to play (for example, an heir's
primary role is usually a spouse, child or other family member, but perhaps
it's an old lady leaving all her money to the nearest cat hospital). There
may be more than one person playing a role for a person (multiple ex-spouses,
children, heirs). If you need more detail, you may want to add a qualifier
to the type (to denote a precedence of inheritance, multiple beneficiaries,
spouse sequence, child order (first born, second, third, etc).
Just some thoughts off the top of my head...
tlj
El Dom 10 Ago 2003 06:00 PM, ethan@ethanzimmerman.com escribió:
> 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?
>
>
>
>
> -----------------------------------------------------------------------
> 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 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:24:10 EDT