Re: Foreign Key problems
Posted by:
Rick James
Date: September 15, 2014 11:08AM
> Are you saying that I don't need to link these tables in any way? I guess that would mean I would not have to define any primary keys either?
You do need to link them. You just don't have to say "FOREIGN KEY". Generally, the INDEXes you have provided are good.
A PRIMARY KEY is an INDEX. Without such, performance is bad. Each table _should_ (some say _must_) have a PRIMARY KEY. If there is a 'natural' PK, use it. You have said (INDI,SOUR) is such a natural PK; use it.
I prefer to write the SELECT statements, then derive the necessary INDEX(es) from them.
Oh. I am assuming a 'parent' is a 'person', so I used only one table, not two. And that JOIN is called a "self join" since the table is being joined to itself.
In your `PARENTS` table (which I will rename `Unions`):
Unions table -- with 0,1,2 persons -- the fields will be NULLable.
A new Relations table -- with an id linking to the Unions table and to a Person. Also a field describing the linkage: biological parents, adopted parents, etc.
This is more complex, but it allows for adoptions, second marriages, unknown parentage, gay marriage, and other nuisances you will encounter in Genealogy. Note that a person can have multiple links.
To find your parents::
SELECT p1.NAME, p1.gender, p2.NAME, p2.gender
FROM PEOPLE AS p
JOIN Relations AS r ON r.INDI = p.INDI
JOIN Unions AS u ON u.id = r.union_id
LEFT JOIN PEOPLE AS p1 ON p1.INDI = u.partner1 AND p1.SOUR = p.SOUR
LEFT JOIN PEOPLE AS p2 ON p1.INDI = u.partner2 AND p2.SOUR = p.SOUR
WHERE p.NAM1 = 'Paul' AND p.NAM2 = 'Reiser';
It would return multiple rows in case you were adopted.
The SELECT I wrote above also 'needs' this on the table PEOPLE:
INDEX(NAM1, NAM2)
I used LEFT JOIN in case a parent is unknown.
> `SOUR` VARCHAR(32) NOT NULL,
Another tip...
The PK of `SOURCES` and in other tables:
`SOUR` TINYINT UNSIGNED NOT NULL
and AUTO_INCREMENT and PRIMARY KEY(SOUR) in the `SOURCES` table.
INDEX `SOUR_idx` (`SOUR` ASC), -- probably useless?
Subject
Views
Written By
Posted
1577
September 12, 2014 10:46PM
825
September 13, 2014 09:18PM
859
September 14, 2014 02:39AM
864
September 14, 2014 01:15PM
1128
September 14, 2014 07:40PM
888
September 14, 2014 09:37PM
887
September 14, 2014 11:07PM
939
September 15, 2014 07:26AM
Re: Foreign Key problems
1065
September 15, 2014 11:08AM
879
September 15, 2014 03:16PM