Как смоделировать в postgres массив, который имеет объекты, каждый объект имеет строку для заголовка и подмассив строк

У меня есть следующий формат в json для хранения юристов, я сомневаюсь, как смоделировать в postgres поле «специальности», которое имеет массив объектов, каждый из которых имеет заголовок и подмассивы подразделов:

{
  "id": 1,
  "name": "John Johnson Johannes",
  "gender": "f",
  "specialties": [
    {
      "specialty": "Business law",
      "sub-specialties": [
        "Incorporation",
        "Taxes",
        "Fusions"
      ]
    },
        {
      "specialty": "Criminal law",
      "sub-specialties": [
        "Property offenses",
        "Personal offenses",
        "Strict liability"
      ]
    }
  ]
}

И я сделал эту таблицу юристов в Postgres:

DROP DATABASE IF EXISTS lawyers_db;
CREATE DATABASE lawyers_db;

\c lawyers_db;

CREATE TYPE gen AS ENUM ('f', 'm');

CREATE TABLE lawyers_tb (
  ID SERIAL PRIMARY KEY,
  name VARCHAR,
  gender gen
);

INSERT INTO lawyers_tb (name, gender)
  VALUES ('John Doe', 'm');

Я использую некоторые библиотеки node.js, которые, когда я читаю данные из таблицы Postgres, они возвращают данные в виде JSON, поэтому я хотел бы сохранить реляционную модель без использования JSONb для хранения в качестве документа моих юристов.

Можно ли добиться того, чего я хочу, без использования типа JSONb?


postgresql node.js postgresql-9.4 postgresql-9.3 postgresql-9.2
person Jeka    schedule 08.06.2016    source источник
comment
Вы не можете использовать 9.2, 9.3 и 9.4 одновременно. Который из них?   -  person e4c5    schedule 09.06.2016

Ответы (1)


arrow_upward
0
arrow_downward

Забудьте на минуту об объектах и ​​по-настоящему подумайте, что такое ваши данные и как они соотносятся друг с другом (в конце концов, мы используем реляционную базу данных).

У вас здесь просто отношения.

У вас есть юристы, и у вас есть специальности. Отношения состоят в том, что юристы имеют специальности, а специальности принадлежат юристам (отношение n-to-n), и то же самое касается отношений между специальностями и узлами (n-to-n).

Во-первых, давайте сделаем более простую структуру отношения 1 к n:

CREATE TABLE lawyers_tb (
  ID SERIAL PRIMARY KEY,
  name VARCHAR,
  gender gen
);

CREATE TABLE specialties_tb (
  ID SERIAL PRIMARY KEY,
  name VARCHAR,
  lawyer_ID INTEGER
);

CREATE TABLE subspecialties_tb (
  ID SERIAL PRIMARY KEY,
  name VARCHAR,
  specialty_ID INTEGER
);

Это работает, но приводит к дублированию, потому что каждая специальность может принадлежать только одному юристу, поэтому, если два юриста специализируются на «Деловом праве», вам придется определять «Деловое право» дважды. Хуже того, для каждой специальности вам также придется продублировать узлы.

Решением является объединенная таблица (также называемая таблицей карты / сопоставления):

CREATE TABLE lawyers_tb (
  ID SERIAL PRIMARY KEY,
  name VARCHAR,
  gender gen
);

CREATE TABLE lawyer_specialties_tb (
  name VARCHAR,
  lawyer_ID INTEGER,
  specialty_ID INTEGER
);

CREATE TABLE specialties_tb (
  ID SERIAL PRIMARY KEY,
  name VARCHAR
);

CREATE TABLE specialty_subspecialties_tb (
  name VARCHAR,
  specialty_ID INTEGER,
  subspecialty_ID INTEGER
);

CREATE TABLE subspecialties_tb (
  ID SERIAL PRIMARY KEY,
  name VARCHAR
);

Таким образом, каждая специальность может принадлежать более чем одному юристу (истинное отношение n-to-n), а каждая узкая специальность может принадлежать более чем одной специальности.

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

SELECT lawyers_tb.name as name,
       lawyers_tb.gender as gender,
       specialties_tb.name as specialty,
       subspecialties_tb.name as subspecialty
FROM lawyers_tb LEFT JOIN lawyer_specialties_tb
         ON lawyers_tb.ID=lawyer_specialties_tb.lawyer_ID
     LEFT JOIN specialties_tb
         ON specialties_tb.ID=lawyer_specialties_tb.specialty_ID
     LEFT JOIN specialty_subspecialties_tb
         ON specialties_tb.ID=specialty_subspecialties_tb.specialty_ID

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

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

person slebetman    schedule 08.06.2016
comment
Большое спасибо за ваше объяснение. Правильный ли сценарий для lawyer_specialties_tb? Я не понимаю, зачем нужны 3 разных идентификатора в этой таблице? Также не должен lawyer_ID быть внешним ключом? - person Jeka; 09.06.2016
comment
Ах. Ага. Копирую наклеил. Я это исправлю. - person slebetman; 09.06.2016
comment
И да, ключи в таблицах соединения должны быть определены как внешние ключи для обеспечения ссылочной целостности. Но это не влияет на структуру или запрос. - person slebetman; 09.06.2016
comment
какова концепция / название lawyer_specialties_tb и specialty_subspecialties_tb, они кажутся просто связующим звеном между другими таблицами. Я хотел бы узнать больше об этой концепции, чтобы по-настоящему понять ее - person Jeka; 09.06.2016
comment
Я упомянул их в ответе: таблицы соединений (также иногда называемые таблицей карт или таблицей сопоставлений) - person slebetman; 09.06.2016