[Solved] Referencing a partner (spouse) in same table

Creating tables and queries

[Solved] Referencing a partner (spouse) in same table

Postby bodvar » Fri Apr 08, 2016 12:59 pm

I am creating a membership database. It includes members, their addresses, their position within the camps and within the association. They have spouses that may or may not be members. And they may have remarried, etc.

The men and the wives are (as of yet) members of different camps: we have men's camps and women's camps which are referenced from a different table. So it is easy to put all the members into the same table, but it seems to me to be redundant when referencing the spouse (and his/her camp) to enter the name for the second time (adding a field that most often includes the name of a member already in the table).

Is there a way to reference the spouse with a "foreign key" of some sort within the same table?
Last edited by Hagar Delest on Fri Apr 08, 2016 9:16 pm, edited 1 time in total.
Reason: tagged [Solved].
OOo 3.0.X on Ms Windows XP + Linux
Posts: 5
Joined: Fri Nov 21, 2008 2:18 pm

Re: Referencing a partner (spouse) in same table

Postby MTP » Fri Apr 08, 2016 3:44 pm

As long as each member will only have the single association (e.g. only spouse and never, say, spouse + children), it would be fine to enter the spouse's memberID instead of their name in the "Spouse" column. That way the address, associated camp, position, etc. can all be referenced to their row of the member table. You can set up a foreign key relationship between the two columns to prevent accidental entries of invalid memberIDs.

If a member might be associated with multiple other people, it would probably work better to set up a separate "join table" to document all the relationships.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Referencing a partner (spouse) in same table

Postby eremmel » Fri Apr 08, 2016 3:46 pm

You have to add to that table a 'spouse_id' column that refers back to an other id in the same table.

But you did already mentioned a complicating factor like remarried, or you like to store parent / child relations. That spouse_id column is sufficient when you what to register only the current state of marriage in respect to the former. You can add extra tables that lists the relation ships. Have something like:
tRelationship( from_id, to_id, Relation_id) with from_id / to_id refer to membership and relation_id refer to tRelationTag.
tRelationTag (id, name) with id just a number and name is the kind of relations to register: Spouce, FormerSpouce, Child, Parent, GrandParent, ...

Note that the extra tables result in extra work. When you add a spouse you need to enter two entries one for each relation. When you add a child you might need to maintain 4 relationships.
It's Microsoft marketing that tells you computers are qualified for non-technicians
Posts: 1041
Joined: Tue Dec 30, 2008 1:15 am

Re: Referencing a partner (spouse) in same table

Postby bodvar » Fri Apr 08, 2016 7:57 pm

Thank you. Both MTP and eremmel's replies were helpful and cleared my mind although I had been thinking along the same path.
I should probably mark this as solved even if I have not tested the suggestion yet. :)
OOo 3.0.X on Ms Windows XP + Linux
Posts: 5
Joined: Fri Nov 21, 2008 2:18 pm

Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 3 guests