父子关系

Question

我想设计一个存储家庭成员的数据库，并且可以构建查询来查找谁是其父亲。总之一个父亲的儿子关系。

这是我想出了

家庭

| id  |  Name | 
--------------------------- 
| 1  | Ankit  | 
--------------------------- 
| 2  | Nishant | 

...... 

以及有关这个发现儿子和父亲 的我创建另一个表

父亲的关系

| father_id | Son_id | 
-------------------------------- 
| 1   |  2  | 
------------------------------- 
..... 

我觉得它不正确，有人可以指导我，并且需要写什么查询来获得这样的关系。

在此先感谢

编辑

确定我试着查询，但现在不知何故，我正在错误 这是我在做什么

select f.name as father_name, s.name as son_name 
from (select family.name from family,father where father.father_id = family.id) as f Inner Join 
(select family.name from family,father where father.son_id = family.id) as s 
on 
(family.id = father.father_id and family.id = father.son_id) 

误差

Msg 4104, Level 16, State 1, Line 2 
The multi-part identifier "family.id" could not be bound. 
Msg 4104, Level 16, State 1, Line 2 
The multi-part identifier "father.father_id" could not be bound. 
Msg 4104, Level 16, State 1, Line 2 
The multi-part identifier "family.id" could not be bound. 
Msg 4104, Level 16, State 1, Line 2 
The multi-part identifier "father.son_id" could not be bound. 

Answer 1

做这件事不，那里有什么错。

你可以写一个联接查询基于ID用在关系表加入到本身

SELECT dad.name AS fathers_name, son.name AS sons_name 
FROM father AS R 

INNER JOIN family AS dad ON dad.id = R.father_id 
INNER JOIN family AS son ON son.id = R.son_id 

编辑：

这是我的SQL Server版本。我有这个工作没有任何问题。

Answer 2

您的设计可以工作。它没有什么“错误”。

但还有另一种方法。

您可以在FAMILY表中指定父行的FATHER_ID列。这是一个指向自己的外键。你不需要这样一个单独的表。

Answer 3

你有什么会工作得很好，但你可以用一个表

Id Name  Father 
1 Ankit  Null 
2 Nishant 1 

Answer 4

你的表的设计是正确的，但我会如果你需要你的祖先，你应该使用一个CTE的整个树改名为表Person和RelationShip

SELECT 
    FamliyFather.name AS FatherName 
    , FamliySon.name AS SonName 
FROM 
    Family AS FamliyFather 
    INNER JOIN Father 
    ON FamliyFather.id = Father.father_id 
    INNER JOIN Family AS FamliySon 
    ON Father.son_id = FamliySon.id 

。