Using a table column twice as compound foreign key

Question

4.00/5 (2 votes)

See more:

Hi,

I have a database holding family tree data.
I have a table called person, with all relevent details about them personally (name, date of birth, birthplace, gender)

I need some way of managing relationships bewteen the people in my database. I would like to have another table made of a compound foreign key, with two personID's from the person table.

Is this possible? or are there any obvious workarounds?
I will be implementing the database using hsqldb.

Thanks in advance
Joe Sutton

Posted 14-Mar-11 5:18am

Member 7726070

Add a Solution

Comments

Kschuler 14-Mar-11 11:42am

Might help if you talk about why you want the table. If it's a table of marriages you may have a problem because I've heard of people getting married...then divorced...then married again to the same person. Which would throw a wrench in your process unless you added a date field to the key.

Member 7726070 14-Mar-11 11:46am

This is a databse to keep family tree data. Divorces/re-marriages won't be a problem right now. I need a way to be able to reference differenct occurences of the same primary key in one table as a compound foreign key in another. i.e. PARENT references Person.ID, CHILD references Person.ID

Is this possible?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Manfred Rudolf Bihy · Accepted Answer · 2011-03-14T06:33:00

Of course you can have a table like that! Let's call the relation (table) "DirectAncestors" for example:

Table DirectAncestors:
PersonId bigint; (Foreign key to table Person.ID)
FatherID bigint; (Foreign key to table Person.ID) 
MotherID bigint; (Foreign key to table Person.ID)

The table Person should have an autoincrement primary key and a unique index on the fields marked *:

Table Person:
ID, (autoincrement)
GivenName, *
Name, *
DOB, *
DOD, (nullable),
Birthplace, *
Gender *

You can also have the table Person have a foreign key relation to itself:

Table Person:
ID (autoincrement)
GivenName *
Name *
DOB, *
DOD (nullable),
Birthplace, *
Gender *, 
MotherID, ( Foreign Key to Person.ID nullable ),
FatherID, ( Foreign Key to Person.ID nullable )

The first solution is preferable when you want to traverse the ancestry in both directions.

Hope that help you a little in your decision.

Cheers!