Есть ли способ эффективно поддерживать правила крупномасштабных данных в SQL?

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

Чтобы выявить и просмотреть эти ошибки, я внедрил правила бизнес-данных. Первоначально управление небольшим количеством правил (2–3) было вполне осуществимо.

Однако, когда количество правил возросло до более чем 50, мне стало сложно их эффективно поддерживать. Можете ли вы дать предложения по эффективному управлению этими правилами данных в SQL?

Пример, который я использую:

WITH base AS
  (
  SELECT 'Apple' AS fruit, 'White' AS color, 1 as price FROM dual
  UNION ALL
  SELECT 'Apple' AS fruit, 'Red' AS color, 0 as price FROM dual
  )
SELECT
  base.*,
  case when base.fruit = 'Apple' and color <> 'Red' then 1 else 0 end as wrong_color,
  case when fruit = 'Apple' and color <> 'Red' then 1 else 0 end as wrong_price
FROM base;

Используйте CHECK ограничения, FOREIGN KEY ограничения и триггеры.

— 28.06.2024 10:59

... и, конечно же, первичные ключи. И используйте систему контроля версий

— 28.06.2024 11:01

sql oracle

28.06.2024 10:39

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

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

Давайте посмотрим, что у нас здесь...

CREATE TABLE fruit_kind
(
  id_fruit_kind  INTEGER GENERATED ALWAYS AS IDENTITY,
  kind           VARCHAR2(50), -- 'Apple', 'Banana', ...
  PRIMARY KEY (id_fruit_kind)
);

CREATE TABLE color
(
  id_color  INTEGER GENERATED ALWAYS AS IDENTITY,
  name      VARCHAR2(50), -- 'red', 'green', ...
  PRIMARY KEY (id_color)
);

CREATE TABLE fruit
(
  id_fruit       INTEGER GENERATED ALWAYS AS IDENTITY,
  id_fruit_kind  INTEGER,
  name           VARCHAR2(5), -- 'Granny Smith', 'Braeburn', ...
  id_color       INTEGER,
  price          NUMBER,
  PRIMARY KEY (id_fruit),
  FOREIGN KEY (id_fruit_kind) REFERENCES fruit_kind (id_fruit_kind),
  FOREIGN KEY (id_color) REFERENCES color (id_color)
);

Теперь вы хотите установить правила. Вид фруктов может встречаться только в определенных цветах и в определенном ценовом диапазоне. Следовательно:

ALTER TABLE fruit_kind ADD min_price NUMBER;
ALTER TABLE fruit_kind ADD max_price NUMBER;

CREATE TABLE allowed_fruit_color
(
  id_fruit_kind  INTEGER,
  id_color       INTEGER,
  PRIMARY KEY (id_fruit_kind, id_color),
  FOREIGN KEY (id_fruit_kind) REFERENCES fruit_kind (id_fruit_kind),
  FOREIGN KEY (id_color) REFERENCES color (id_color)
);

Наконец, давайте создадим триггер для обеспечения корректности:

CREATE OR REPLACE TRIGGER trg_fruit_correctness
AFTER INSERT OR UPDATE ON fruit
FOR EACH ROW
DECLARE
  v_count INTEGER;
BEGIN
  -- Is the fruit price allowed?
  SELECT COUNT(*)
  INTO v_count
  FROM fruit_kind
  WHERE id_fruit_kind = :new.id_fruit_kind
  AND :new.price BETWEEN min_price AND max_price;

  IF v_count = 0 THEN
    RAISE_APPLICATION_ERROR(-20000, 'Price not in allowed range for this fruit type');
  END IF;

  -- Is the fruit color allowed?
  SELECT COUNT(*)
  INTO v_count
  FROM allowed_fruit_color
  WHERE id_fruit_kind = :new.id_fruit_kind
  AND id_color = :new.id_color;

  IF v_count = 0 THEN
    RAISE_APPLICATION_ERROR(-20000, 'Color not in allowed for this fruit type');
  END IF;
END trg_fruit_correctness;
/

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

ОБНОВЛЕНИЕ: Это может быть лучше. Пожалуйста, прочитайте комментарии MT0 ниже.

Вам также нужен триггер на fruit_kind, чтобы, если min_price и max_price обновляются, таблица fruit проверялась на соответствие новым границам, в противном случае вы можете ввести действительные данные о ценах, а затем изменить минимальные/максимальные границы, и вы обнаружите, что теперь у вас есть неверные данные, которые выиграли. не проверяю, но ошибок не возникает, поскольку вы не изменили таблицу fruit. Аналогично для allowed_fruit_colour (и я бы не стал объединять несколько проверок в одном триггере — используйте один триггер для цены, а другой — для цвета).

— 28.06.2024 16:12

Если подумать, allowed_fruit_colour не должно быть триггером - должно быть ограничение внешнего ключа fruit на составном ключе id_fruit_kind, id_color

— 28.06.2024 16:25

@MT0: Да, ты прав. Спасибо.

— 28.06.2024 17:36

28.06.2024 16:03