Как реализовать внешний ключ в такой ситуации?

Question

[Abdula Magomedov]

Привет!

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

transaction(id, type, sum, date)

Есть еще таблица в которую записаны факт оплаты некой услуги. Событие оплаты услуги связано с событием "расхода" с счета. Для этого я создаю таблицу

service_pay(service_id, transaction_id)

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

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

Дайте совет, как сделать?

Или все же придется записывать приходы и расходы в разных таблицах?

...

Answer 1

[1011]

что мешает создать одну таблицу?
transaction(id, service_id, type, sum, date, и тут столбец суммы прихода со знаком плюс, а сумма расхода будет со знаком минус)

Answer 2

[zergon321]

Сделать определение таблицы service_pay так:

create table service_pay
(
service_id ...,
transaction_id ... CHECK (transaction_id in (select id from transaction_table where type = ...)) #тип расходной транзакции
foreign key (transaction_id) references transaction(id)
);

Или можно создать триггер на таблицу service_pay:

DELIMITER //

CREATE TRIGGER service_pay_bi
BEFORE INSERT ON service_pay
FOR EACH ROW
BEGIN
IF NEW.transaction_id IN (SELECT id FROM transaction_table WHERE type = ...) THEN #указать значение события дохода
@last_error = "service_pay table can hold only the payment transactions";
CALL non_existent(); #единственный способ прервать операцию
END IF;
END//

А можно транзакции дохода и расхода хранить в разных таблицах, но в расходных добавить столбец service_id, тогда таблица service_pay не нужна. Если нужна выборка из всех транзакций - пользоваться UNION.

SELECT ...
FROM tbl_1
UNION
SELECT ...
FROM tbl_2;

Comments

[Abdula Magomedov]

Третья идея мне понравилась, на счет первой, в MySQL к сожалению не работает CHECK

Answer 3

[Freeman]

Самым простым мне кажется добавить поле TYPE в таблицу SERVICE_PAY, а саму таблицу переименовать в SERVICE_TRANSACTIONS, сразу закладываясь на будущее и подразумевая, что не только клиент может платить сервисам, но и сервисы могут возвращать ему деньги или выплачивать вознаграждения, просто эта возможность в первой версии еще не реализована.