在SQL中创建具有父/子层次结构的表/表

Question

我目前是一个自学成才的程序员，我知道并使用过noSQL数据库。现在我想更好地学习关系数据库。我在为自己做一个项目，试图加深我在这里的知识，我有点困惑。我的目标是创建一个具有子/父层次结构的简单数据库。我想为个人创建一个数据库，在这个数据库中，我只能访问身份证号(没有姓名、年龄或其他任何东西)。这个人可以有孩子，也可以有父母，或者两者都有。你建议我如何组织这个。我想查询表，所以我可以检索某个人的所有孩子和父母。我的第一个想法是建立一个表，只有PK(Id_Number)和其他表与复合键(FK->Id_Number和Id_parent)，但我不认为它会这样工作?我该怎么做呢?你对此有什么想法吗?谢谢你！

为了帮助可视化(而不是识别号码，让我们使用名称)，我有这个对象，我应该如何存储它?

{
human: "Joe",
child: [
{
human: "Kevin",
child: [
{
human: "Joaquin",
},
{
human: "leticia",
},
],
},
{
human: "Mary",
child: [
{
human: "Joaquin",
child: [{ human: "levi" }],
},
{
human: "leticia",
},
],
},
],
};

目标是了解所有的家庭等级，比如谁是儿子，谁是父母，谁可能是凯文的兄弟。

Answer 1

为实体之间的父关系建模的最简单方法是为实体(图中的节点)使用一个表，为关系(图中节点之间的弧线)使用另一个表。

例如:

create table person (
id int primary key not null,
identification_number varchar(50) not null
);
create table parent_child_relationship (
parent_id int not null references person (id),
child_id int not null references person (id),
primary key (parent_id, child_id),
);

您也可以使用单个表，但是将关系从实体中分离出来，使您能够灵活地考虑有多个父母(亲生父母、合法父母、收养父母等)的人，并考虑生活变化。

Answer 2

这就是我如何在AWS红移中创建一个统一的层次结构。

这将为层次树中的每个节点创建从上到下的层次结构

示例数据模式:

create table kpi_dashboard.employee (
id int,
name varchar (20),
manager_id int
);

插入数据

insert into schema_name.employee(id, name, manager_id)  values
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);

生成表:

id  name    manager_id
100 Carlos  
101 John    100
102 Jorge   101
103 Kwaku   101
110 Liu 101
106 Mateo   102
110 Nikki   103
104 Paulo   103
105 Richard 103
120 Saanvi  104

创建统一的层次结构路径的递归SQL -

with recursive Hierarchy (id,name,manager_id,Path)
AS
(
SELECT t.id,t.name,t.manager_id, CAST(t.name AS varchar(max))
FROM schema_name.employee AS t
LEFT JOIN schema_name.employee AS t1
ON t1.id = t.manager_id 
WHERE t1.id IS NULL
UNION ALL
SELECT t.id,t.name,t.manager_id, CAST(h.Path + '>' + t.name AS varchar(max))
FROM Hierarchy h
INNER JOIN schema_name.employee AS t
ON t.manager_id = h.id
)
SELECT id,name,Path
FROM Hierarchy

输出:

id  name    path
100 Carlos  Carlos
101 John    Carlos>John
110 Liu Carlos>John>Liu
103 Kwaku   Carlos>John>Kwaku
102 Jorge   Carlos>John>Jorge
105 Richard Carlos>John>Kwaku>Richard
104 Paulo   Carlos>John>Kwaku>Paulo
110 Nikki   Carlos>John>Kwaku>Nikki
201 Sofía   Carlos>John>Jorge>Sofía
106 Mateo   Carlos>John>Jorge>Mateo