Каким образом сохранить целостность данных в базе без триггеров?

Question

[Q R]

Каким образом сохранить целостность данных в базе без триггеров?
Предположим, у нас есть счета, которые выставляются разным клиентам и услуги, предоставляемые разным клиентам. Хотелось бы на уровне индексов запретить выставление счетов на те услуги, которые данной компании не предоставлялись.
Схема базы:

CREATE TABLE `customers` (
	`id` INT(10) NOT NULL,
	`name` varchar(20) NOT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE `bills` (
	`id` INT(10) NOT NULL,
	`number` varchar(6) NOT NULL,
	`customer` INT(10) NOT NULL,
	`creation_date` DATE NOT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE `services` (
	`id` INT(10) NOT NULL,
	`service_name` varchar(50) NOT NULL,
	`customer` INT(10) NOT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE `Untitled` (
	`id` INT NOT NULL,
	`bill` INT NOT NULL,
	`service` INT NOT NULL,
	PRIMARY KEY (`id`)
);

ALTER TABLE `bills` ADD CONSTRAINT `bills_fk0` FOREIGN KEY (`customer`) REFERENCES `customers`(`id`);

ALTER TABLE `services` ADD CONSTRAINT `services_fk0` FOREIGN KEY (`customer`) REFERENCES `customers`(`id`);

ALTER TABLE `Untitled` ADD CONSTRAINT `Untitled_fk0` FOREIGN KEY (`bill`) REFERENCES `bills`(`id`);

ALTER TABLE `Untitled` ADD CONSTRAINT `Untitled_fk1` FOREIGN KEY (`service`) REFERENCES `services`(`id`);

То есть, каким образом запретить в таблице `bills` возможность вставлять данные об услугах, оказанных для других компаний?

Заранее спасибо.

Answer 1

[Алексей]

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

START TRANSACTION;
INSERT INTO `bills`(`custom_id`) VALUES ($ид_клиента);

$bill_id = mysqli_последний_вставленный_ид

И добавляем какие услуги нужно предоставить. Проверку выполнена она или нет, сами сделаете.

foreach ($массив с ИД зказанных услуг) {
INSERT INTO `order_service`(`bill_id`,`service_id`) VALUES ($bill_id,$service_id);
};
COMMIT;

Тоесть получаем 1 счет на 1 клиента и хоть ты убей не получится в счет вписать еще 1 клиента. Все услуги будут привязанны именно к этому счету.

После выполнения собираем все выполненные заказанные услуги и выставляем клиенту:

SELECT `service_id` 
FROM `order_service`  
WHERE `bill_id`=$по счету

Названия и прочее добивать джойнами.

Comments

[Q R]

Вы могли бы подробнее объяснить, если не сложно?

[Q R]

Всё, вроде сообразил, что вы имеете в виду. Нет, это не подойдёт, так как, проблема не в повторном выставлении счёта за услугу, а именно в том, чтобы клиент-получатель счёта был клиентом-получателем услуги

[Алексей]

Роман Рубашкин: у вас вообще не понятное построение реляционной модели, уберите из таблицы сервисов customers, оставляя там только название для запросов и добавьте в таблицу bills все контролирующие элементы и стройте индексы по ним. Вообще самый простой ответ, не добавляйте счет, если услуга не предоставлялась и все, а если предоставлялась, то записывайте сразу в bills

[Q R]

Алексей: А если в одном счёте может быть несколько услуг и одна услуга может быть в нескольких счетах?

[Алексей]

Роман Рубашкин: ну тогда вам вообще все надо переделывать, через несколько минут исправлю свой ответ.

[Q R]

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

[Q R]

Алексей: Никак не могу понять, как это поможет избежать вставку в счёт услуги, которая была оказана, но оказана другой компании.(

[Алексей]

Роман Рубашкин: я не вижу у вас таблицы компаний, у вас есть клиенты, есть услуги и все. О каких компаниях идет речь вообще не понятно, сформулируйте четко вопрос по предоставленным данным, без разброса мыслей и синонимов.

[Алексей]

Роман Рубашкин: а еще лучше опишите свой сервис, чтоб было более понятно как, кто и за что платит и какие звенья нужно исключить

[Q R]

Алексей: Компания занимается грузоперевозками и работает с юридическими лицами.
На данный момент сервис позволяет выставить счёт и привязать его к совершённым перевозкам.
Но, никаким образом не исключается коллизия, когда в один счёт могут попасть перевозки, совершённые для разных клиентов.
Допустим, есть клиенты А,Б,В. Мы выставляем счёт для А и привязываем к нему перевозки. Но, у нас нет никакого ограничения на возможность в счёт А вставить перевозки, совершённые для Б.
Клиенты=компании.

[Алексей]

Роман Рубашкин: надо не счет привязывать к перевозкам, а перевозки к счету.

[Q R]

Алексей: Идею понял, большое спасибо!

Answer 2

[Дмитрий Ковальский]

Вариант написать хранимую процедуру с транзакцией добавления данных с нужными проверками не рассматриваете?

Comments

[Q R]

Рассматриваю, но, как крайний случай. Я новичок в sql, но, мне кажется, что должны быть возможности для решения этой задачи методами без процедур. Если нет, то буду копать информацию о процедурах.