So I have data arranged loosely like this:
所以我的数据排列如下:
Table 1 - PEOPLE: person_id (primary key), parent_id, child_id, other_parent_fields, other_child_fields
表1 - PEOPLE:person_id(主键),parent_id,child_id,other_parent_fields,other_child_fields
Table 2 - PARENTS: parent_id (auto incrementing primary key), other_fields Table 3 - CHILDREN: child_id (auto incrementing primary key), parent_id(foreign key referencing PARENTS) other_fields
表2 - PARENTS:parent_id(自动递增主键),other_fields表3 - CHILDREN:child_id(自动递增主键),parent_id(引用PARENTS的外键)other_fields
I want to be able to query for all of the distinct parents from the PEOPLE table, and insert all of the other_parent_fields into the PARENTS table, throwing out the old parent_id from Table 1, in favor of my auto incrementing parent_id in table 2.
我希望能够从PEOPLE表中查询所有不同的父项,并将所有other_parent_fields插入PARENTS表中,从表1中抛出旧的parent_id,以支持表2中的自动递增parent_id。
I also want to do the same for children, but maintain the parent-child relationships, only using my own ids from table 2 and table 3.
我也想为孩子们做同样的事情,但是只保留父子关系,只使用我自己的表2和表3中的id。
Essentially, I am trying to change the way that the database is designed. Rather than a whole table for all people, I am creating a PARENTS table and a CHILDREN table, the latter of which refers to PARENTS with a foreign key. The reason I am throwing out the ids from table 1 is because I have no reason to care about them in my new table (i.e. the numbering can start back from one, and additional entries can just auto increment the primary key). However, before discarding these IDs from table 1, I need to capture the parent-child relations that they relay.
基本上,我试图改变数据库的设计方式。我创建了一个PARENTS表和一个CHILDREN表,而不是一个适用于所有人的整个表,后者使用外键引用PARENTS。我从表1中丢弃id的原因是因为我没有理由在我的新表中关注它们(即编号可以从一个开始,而其他条目可以自动增加主键)。但是,在从表1中丢弃这些ID之前,我需要捕获它们中继的父子关系。
Is this even possible? How would one go about doing it?
这有可能吗?怎么会去做呢?
we can assume, for simplicity that no children have children i.e. someone cant be a parent and a child
我们可以假设,为简单起见,没有孩子有孩子,即某人不能成为父母和孩子
1 个解决方案
#1
2
I did not fully understand your question but it seems that you first query would be this (SQL Server syntax):
我没有完全理解你的问题,但似乎你第一次查询将是这个(SQL Server语法):
insert into Parents
select other_parent_fields, person_id as legacy_parent_id
from (select distinct person_id, other_parent_fields from PEOPLE where parent_id is null) x
The trick would be to first group on parent_id, other_parent_fields and then discard the parent_id. (A distinct is equal to a group by *). The above query only works if other_parent_fields is a pure function of parent_id. I interpret your question as an attempt to normalize denormalized data, so I guess this is true.
诀窍是首先对parent_id,other_parent_fields进行分组,然后丢弃parent_id。 (一个不同的是等于一个组*)。仅当other_parent_fields是parent_id的纯函数时,上述查询才有效。我将你的问题解释为尝试归一化非规范化数据,所以我想这是真的。
In order to extract the children you can do this:
为了提取孩子,你可以这样做:
insert into Children
select other_child_fields, parent_id as legacy_parent_id
from (select distinct person_id, other_child_fields from PEOPLE where parent_id is not null) x
Now your tables contain the distinct parents and children as well as their old IDs. You have to write an update query now that assigns the new parent ids into the children table. Then you drop the legacy fields.
现在,您的表包含不同的父项和子项以及旧ID。您现在必须编写一个更新查询,将新的父ID分配给子表。然后删除旧字段。
#1
2
I did not fully understand your question but it seems that you first query would be this (SQL Server syntax):
我没有完全理解你的问题,但似乎你第一次查询将是这个(SQL Server语法):
insert into Parents
select other_parent_fields, person_id as legacy_parent_id
from (select distinct person_id, other_parent_fields from PEOPLE where parent_id is null) x
The trick would be to first group on parent_id, other_parent_fields and then discard the parent_id. (A distinct is equal to a group by *). The above query only works if other_parent_fields is a pure function of parent_id. I interpret your question as an attempt to normalize denormalized data, so I guess this is true.
诀窍是首先对parent_id,other_parent_fields进行分组,然后丢弃parent_id。 (一个不同的是等于一个组*)。仅当other_parent_fields是parent_id的纯函数时,上述查询才有效。我将你的问题解释为尝试归一化非规范化数据,所以我想这是真的。
In order to extract the children you can do this:
为了提取孩子,你可以这样做:
insert into Children
select other_child_fields, parent_id as legacy_parent_id
from (select distinct person_id, other_child_fields from PEOPLE where parent_id is not null) x
Now your tables contain the distinct parents and children as well as their old IDs. You have to write an update query now that assigns the new parent ids into the children table. Then you drop the legacy fields.
现在,您的表包含不同的父项和子项以及旧ID。您现在必须编写一个更新查询,将新的父ID分配给子表。然后删除旧字段。