Архитектура базы данных

Question

Возникла проблема с созданием архитектуры базы данных.

CREATE TABLE recipes (
    id INTEGER NOT NULL,
    kind VARCHAR,
    name VARCHAR,
    ingredients JSON,
    time VARCHAR,
    hash VARCHAR,
    PRIMARY KEY (id)
);

Не понимаю как нужно хранить ingredients. Ну то-есть хранить то ладно - оно хранится, но вот не знаю как далее делать запрос через какой-нибудь ингредиент.

Пример:

{
    "name": "Some recipe",
    "ingredients": [
      {
        "name": "Вода",
        "amount": "1 л"
      },
      {
        "name": "Сахар",
        "amount": "90 г"
      }
    ],
    "time": "30 минут"
},
{
"name": "Some recipe2",
    "ingredients": [
      {
        "name": "Соль",
        "amount": "1 г"
      },
      {
        "name": "Перец",
        "amount": "90 г"
      }
    ],
    "time": "60 минут"
}

Все данные свыше идут в бд и вот вопрос:
Как получить, допустим, второй рецепт зная только ингредиент, допустим, соль?

Также пробовал сделать отдельную таблицу ingredients в стиле:

 self._table: Table = Table('ingredients', metadata,
                               Column('id', Integer, primary_key=True),
                               Column('name', String)
                               )

Но опять же возникает вопрос: "Как в данном случае хранить amount"?

Answer 1

Хранить в JSON можно, только у этого есть неприятные последствия (об этом ниже).

Можно воспользоваться функцией json_each:

select r.id from (
  select r.id, json_each.value single_ing
  from (
      select r.id, json_each.value as ing_array 
      from recipes r, json_each(recipes.ingredients)
      where json_each.key = 'ingredients'
  ) r, json_each(ing_array)
) r, json_each(single_ing)
where json_each.key = 'name' and json_each.value = 'Соль';

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

Во-вторых, производительность такого запроса на поиск по полю в json будет мягко говоря не очень.

Для денормализации нужны веские причины, если не можете их сформулировать - лучше начните с нормализованных данных.

БЛОГ НА HUSL

Архитектура базы данных

Пример:

Ответы (1 шт):