How to implement a foreign key in such a situation?

Question

MySQL

Hey!
There is a table where the receipt and expenditure of money to the account are recorded.

transaction(id, type, sum, date)

There is also a table in which the fact of payment for a certain service is recorded. The service payment event is associated with the "expenditure" event from the account. For this I create a table

service_pay(service_id, transaction_id)

and create a foreign key on the transaction table , for integrity. But there is one loophole here, a foreign key can refer to both an expense and an income, which violates the integrity. How can this problem be solved?
For a solution, I could add another column to the payment table, with a value representing the expense, and make a composite foreign key. But then I will get a column in which all rows will have the same value, dumb. And in general, even in this case, how to make it possible to write a single value to a column, because MySQL does not support check contrains
Give advice on how to do it?

Or do you still have to record income and expenses in separate tables?

...

Answer 1

what hinders to create one table?
transaction(id, service_id, type, sum, date, and here the column of the amount of income is with a plus sign, and the amount of expense will be with a minus sign)

Answer 2

Make the service_pay table definition like this:

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)
);

Or you can create a trigger on the service_pay table:

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//

Or you can store income and expense transactions in different tables, but add the service_id column in the expense ones, then the service_pay table is not needed. If selection from all transactions is necessary - to use UNION.

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

Answer 3

It seems to me that the simplest thing is to add the TYPE field to the SERVICE_PAY table, and rename the table itself to SERVICE_TRANSACTIONS, immediately laying down the future and implying that not only the client can pay for services, but also services can return money to him or pay rewards, it's just that this feature is in the first version not implemented yet .