Разделить таблицу во многих отношениях: миграция данных

Мне интересно, как лучше всего переносить мои данные при разбиении таблицы на многие отношения. Я сделал упрощенный пример, и я также опубликую некоторые из решений, которые я придумал. Я использую базу данных Postgresql.

Перед миграцией

Персонал таблицы


ID       Name        Pet        PetName
1        Follett     Cat        Garfield
2        Rowling     Hamster    Furry
3        Martin      Cat        Tom
4        Cage        Cat        Tom

После миграции

Персонал таблицы


ID       Name
1        Follett
2        Rowling
3        Martin
4        Cage

Таблица Pet


ID       Pet        PetName
6        Cat        Garfield
7        Hamster    Furry
8        Cat        Tom
9        Cat        Tom

Таблица PersonPet


FK_Person     FK_Pet
1             6
2             7
3             8
4             9

Примечания:

Я буду дублировать записи в таблице домашних животных (потому что в моем случае - из-за других связанных данных - один из них может быть доступен для редактирования клиентом, а другой - нет).
Нет столбца, который однозначно идентифицирует запись "Pet".
Для меня неважно, связаны ли 3-8 и 4-9 в таблице PersonPet или 3-9 и 4-8.
Также я пропустил весь код, который обрабатывает изменения схемы таблиц, поскольку это - в моем понимании - нерелевантно для этого вопроса.

Мои решения

При создании таблицы Pet временно добавьте столбец, содержащий идентификатор таблицы Person, который использовался для создания этой записи.


    ALTER TABLE Pet ADD COLUMN IdPerson INTEGER;

    INSERT INTO Pet (Pet, PetName, IdPerson)
    SELECT Pet, PetName, ID
    FROM Person;

    INSERT INTO PersonPet (FK_Person, FK_Pet)
    SELECT ID, IdPerson
    FROM Pet;

    ALTER TABLE Pet DROP Column IdPerson;

избежать временного изменения таблицы домашних животных


    INSERT INTO Pet (Pet, PetName)
    SELECT Pet, PetName
    FROM Person;

    WITH
      CTE_Person
      AS
      (SELECT
        Id, Pet, PetName
        ,ROW_NUMBER() OVER (PARTITION BY Pet, PetName ORDER BY Id) AS row_number
      FROM Person
      )
      ,CTE_Pet
      AS
      (SELECT
        Id, Pet, PetName
        ,ROW_NUMBER() OVER (PARTITION BY Pet, PetName ORDER BY Id) AS row_number
      FROM Pet
      )
      ,CTE_Joined
      AS
      (SELECT
        CTE_Person.Id AS Person_Id,
        CTE_Pet.Id AS Pet_Id
      FROM
        CTE_Person
        INNER JOIN CTE_Pet ON
        CTE_Person.Pet = CTE_Pet.Pet
        CTE_Person.PetName = CTE_Pet.PetName
        AND CTE_Person.row_number = CTE_Pet.row_number
      )
      INSERT INTO PersonPet (FK_Person, FK_Pet)
      SELECT Person_Id, Pet_Id from CTE_Joined;

Вопросы

Правильны ли оба решения? (Я тестировал второе решение, и результат кажется правильным, но я, возможно, пропустил какой-то угловой случай)
Каковы преимущества/недостатки двух решений?
Есть ли более простой способ сделать одну и ту же миграцию данных? (Для моего любопытства меня также интересовали бы ответы, которые немного меняют мои ограничения (например, никаких дубликатов записей в таблице домашних животных), но укажите, какие из них:)).

Ответ 1

Другое решение для достижения эффекта, которое вы описали (на мой взгляд, самый простой, без каких-либо CTE-команд или дополнительных столбцов):

create table Pet as
    select
        Id,
        Pet,
        PetName
    from 
        Person;

create table PersonPet as
    select
        Id as FK_Person,
        Id as FK_Pet
    from
        Person;

create sequence PetSeq;
update PersonPet set FK_Pet=nextval('PetSeq'::regclass);
update Pet p set Id=FK_Pet from PersonPet pp where p.Id=pp.FK_Person;

alter table Pet alter column Id set default nextval('PetSeq'::regclass);
alter table Pet add constraint PK_Pet primary key (Id);
alter table PersonPet add constraint FK_Pet foreign key (FK_Pet) references Pet(Id);

Мы просто используем существующий идентификатор лица в качестве временного идентификатора для домашних животных, если только мы не сгенерируем одну используемую последовательность.

Edit

Также возможно использовать мой подход с уже внесенными изменениями схемы:

insert into Pet(Id, Pet, PetName)
    select
        Id,
        Pet,
        PetName
    from
        Person;

insert into PersonPet(FK_Person, FK_Pet)
    select
        Id,
        Id
    from
        Person;

select setval('PetSeq'::regclass, (select max(Id) from Person));

Ответ 2

Да, ваши оба решения верны. Они напоминают мне этот ответ.

Несколько заметок.

Первый вариант с добавлением дополнительного столбца PersonID в таблицу Pet может быть выполнен в одном запросе с использованием предложения RETURNING.

SQL Fiddle

-- Add temporary PersonID column to Pet

WITH
CTE_Pets
AS
(
    INSERT INTO Pet (PersonID, Pet, PetName)
    SELECT Person.ID, Person.Pet, Person.PetName
    FROM Person
    RETURNING ID AS PetID, PersonID
)
INSERT INTO PersonPet (FK_Person, FK_Pet)
SELECT PersonID, PetID
FROM CTE_Pets
;

-- Drop temporary PersonID column

К сожалению, кажется, что предложение RETURNING в INSERT в Postgres ограничено возвратом столбцов только из таблицы назначения, то есть только те значения, которые были фактически вставлены. Например, в MS SQL Server MERGE можно возвращать значения из исходных и целевых таблиц, что облегчает выполнение таких задач, но я не могу найти ничего подобного в Postgres.

Итак, второй вариант без добавления явного столбца PersonID в таблицу Pet требует объединения оригинала Person с новым Pet для сопоставления старого PersonID с новым PetID.

Если в вашем примере могут быть дубликаты (Cat Tom), используйте ROW_NUMBER, чтобы назначать порядковые номера для выделения повторяющихся строк, как показано в вопросе.

Если таких дубликатов нет, вы можете упростить отображение и избавиться от ROW_NUMBER.

INSERT INTO Pet (Pet, PetName)
SELECT Pet, PetName
FROM Person;

INSERT INTO PersonPet (FK_Person, FK_Pet)
SELECT
    Person.ID AS FK_Person
    ,Pet.ID AS FK_Pet
FROM
    Person
    INNER JOIN Pet ON
        Person.Pet = Pet.Pet AND
        Person.PetName = Pet.PetName
;

Я вижу одно преимущество первого метода.

Если вы храните явно PersonID в таблице Pet, будет легче выполнить этот вид миграции несколькими шагами в пакетах. Второй вариант работает нормально, когда PersonPet пуст, но если вы уже перенесли партию строк, может оказаться сложным фильтровать нужные строки.

Ответ 3

Вы можете преодолеть ограничение необходимости добавить дополнительный столбец на стол для домашних животных, вставив сначала в таблицу внешнего ключа, а затем в таблицу домашних животных. Это позволяет определить, что такое отображение, а затем заполнить детали во втором проходе.

INSERT INTO PersonPet
SELECT ID, nextval('pet_id_seq'::regclass) as PetID
FROM Person;

INSERT INTO Pet
SELECT FK_Pet, Pet, Petname
FROM Person join PersonPet on (ID=FK_Person);

Это можно объединить в одно утверждение, используя общие механизмы выражения таблицы, изложенные Владимиром в его ответе:

WITH
fkeys AS
(
  INSERT INTO PersonPet
    SELECT ID, nextval('pet_id_seq'::regclass) as PetID
    FROM Person
  RETURNING FK_Person as PersonID, FK_Pet as PetID
)
INSERT INTO Pet
SELECT f.PetID, p.Pet, p.Petname
FROM Person p join fkeys f on (p.ID=f.PersonID);

Что касается преимуществ и недостатков:

Ваше решение # 1:

Более эффективен с точки зрения вычислений, он состоит из двух операций сканирования, без соединений и типов.
Менее экономичен, потому что он требует хранения дополнительных данных в таблице домашних животных. В Postgres пространство не восстанавливается в столбце DROP (но вы можете восстановить его с помощью CREATE TABLE AS/DROP TABLE).
Может возникнуть проблема, если вы делаете это повторно, например. добавление/удаление столбца регулярно, потому что вы столкнетесь с максимальным пределом столбца Postgres.

Решение, которое я изложил, менее эффективно, чем ваше решение # 1, менее эффективно, потому что для этого требуется соединение, но оно более эффективно, чем решение # 2.