Как я создаю проверочное ограничение нескольких-таблиц?
Вообразите эту маленькую базу данных...
Схема
удаленная мертвая ссылка ImageShack - добровольно предлагает схему базы данных
Таблицы
Volunteer Event Shift EventVolunteer
========= ===== ===== ==============
Id Id Id EventId
Name Name EventId VolunteerId
Email Location VolunteerId
Phone Day Description
Comment Description Start
End
Ассоциации
Волонтеры могут зарегистрироваться в нескольких событиях.
События могут быть укомплектованы несколькими волонтерами.
Событие может иметь несколько сдвигов.
Сдвиг принадлежит только единственному событию.
Сдвиг может быть укомплектован только единственным волонтером.
Волонтер может укомплектовать несколько сдвигов.
Проверочные ограничения
Я могу создать проверочное ограничение для осуществления того сдвига, укомплектован волонтером, это не зарегистрировано в событии того сдвига?
Я могу создать проверочное ограничение для осуществления тех двух, перекрывающиеся сдвиги никогда не укомплектовываются тем же волонтером?
4 ответа
Лучшее место для обеспечения целостности данных - это база данных. Будьте уверены, что какой-нибудь разработчик, намеренно или нет, найдет способ скрыть несогласованные данные в базе данных, если вы им позволите!
Вот пример с проверочными ограничениями:
CREATE FUNCTION dbo.SignupMismatches()
RETURNS int
AS BEGIN RETURN (
SELECT count(*)
FROM Shift s
LEFT JOIN EventVolunteer ev
ON ev.EventId = s.EventId
AND ev.VolunteerId = s.VolunteerId
WHERE ev.Id is null
) END
go
ALTER TABLE Shift ADD CONSTRAINT chkSignup CHECK (dbo.SignupMismatches() = 0);
go
CREATE FUNCTION dbo.OverlapMismatches()
RETURNS int
AS BEGIN RETURN (
SELECT count(*)
FROM Shift a
JOIN Shift b
ON a.id <> b.id
AND a.Start < b.[End]
AND a.[End] > b.Start
AND a.VolunteerId = b.VolunteerId
) END
go
ALTER TABLE Shift ADD CONSTRAINT chkOverlap CHECK (dbo.OverlapMismatches() = 0);
Вот несколько тестов для новых проверок целостности данных:
insert into Volunteer (name) values ('Dubya')
insert into Event (name) values ('Build Wall Around Texas')
-- Dubya tries to build a wall, but Fails because he's not signed up
insert into Shift (VolunteerID, EventID, Description, Start, [End])
values (1, 1, 'Dunbya Builds Wall', '2010-01-01', '2010-01-02')
-- Properly signed up? Good
insert into EventVolunteer (VolunteerID, EventID)
values (1, 1)
insert into Shift (VolunteerID, EventID, Description, Start, [End])
values (1, 1, 'Dunbya Builds Wall', '2010-01-01', '2010-01-03')
-- Fails, you can't start the 2nd wall before you finished the 1st
insert into Shift (VolunteerID, EventID, Description, Start, [End])
values (1, 1, 'Dunbya Builds Second Wall', '2010-01-02', '2010-01-03')
Вот определения таблиц:
set nocount on
if OBJECT_ID('Shift') is not null
drop table Shift
if OBJECT_ID('EventVolunteer') is not null
drop table EventVolunteer
if OBJECT_ID('Volunteer') is not null
drop table Volunteer
if OBJECT_ID('Event') is not null
drop table Event
if OBJECT_ID('SignupMismatches') is not null
drop function SignupMismatches
if OBJECT_ID('OverlapMismatches') is not null
drop function OverlapMismatches
create table Volunteer (
id int identity primary key
, name varchar(50)
)
create table Event (
Id int identity primary key
, name varchar(50)
)
create table Shift (
Id int identity primary key
, VolunteerId int foreign key references Volunteer(id)
, EventId int foreign key references Event(id)
, Description varchar(250)
, Start datetime
, [End] datetime
)
create table EventVolunteer (
Id int identity primary key
, VolunteerId int foreign key references Volunteer(id)
, EventId int foreign key references Event(id)
, Location varchar(250)
, [Day] datetime
, Description varchar(250)
)
Я бы сделал так: в таблице EventVolunteer есть столбец Identity, который автоинкрементируется, с уникальным ограничением на пару EventId, VolunteerId. Используйте EventVolunteerId (идентификатор) в качестве внешнего ключа к таблице Shift. Это обеспечит нужное вам ограничение довольно просто, при этом несколько нормализуя ваши данные.
Я понимаю, что это не ответ на ваш общий вопрос, однако я считаю это лучшим решением вашей конкретной проблемы.
Edit:
Я должен был прочитать вопрос полностью. Это решение не позволит одному добровольцу выполнять две смены на одном мероприятии, даже если они не пересекаются. Возможно, перемещение времени начала и окончания смены в EventVolunteer и наличие ограничения проверки времени в этой таблице было бы достаточно, хотя тогда у вас будут данные смены вне таблицы Shift, что не кажется мне интуитивно понятным.
Есть способ сделать это с помощью триггеров, что я бы не рекомендовал. Я бы рекомендовал не переносить вашу бизнес-логику на уровень базы данных. БД не нужно знать, кто и в какое время укомплектовывает определенную смену. Эту логику следует заложить в уровень вашего бизнеса. Я бы рекомендовал использовать шаблон построения репозитория. У Скотта Гутери есть очень хорошая глава в его книге по mvc 1.0, в которой это описано (ссылка ниже).
http://weblogs.asp.net/scottgu/archive/2009/03/10/free-asp-net-mvc-ebook-tutorial.aspx
Вопрос 1 прост. Просто сделайте так, чтобы ваша таблица Shift ссылалась непосредственно на таблицу EventVolunteer, и все готово