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

Представьте себе эту небольшую базу данных...

Диаграмма

удалена мертвая ссылка ImageShack - диаграмма базы данных добровольцев

Таблицы

Volunteer     Event         Shift         EventVolunteer
=========     =====         =====         ==============
Id            Id            Id            EventId
Name          Name          EventId       VolunteerId
Email         Location      VolunteerId
Phone         Day           Description
Comment       Description   Start
                            End

Ассоциации

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

Событие может иметь несколько сдвигов.
Сдвиг относится только к одному событию.

Смена может быть укомплектована только одним волонтером.
Волонтер может обслуживать несколько смен.

Проверить ограничения

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

Ответ 1

Лучшим местом для обеспечения целостности данных является база данных. Будьте уверены, что какой-то разработчик намеренно или нет, найдет способ проникнуть в базу данных несогласованными, если вы позволите им!

Вот пример с ограничениями проверки:

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

Ответ 2

Вопрос 1 прост. Просто переведите таблицу Shift непосредственно в таблицу EventVolunteer, и вы все настроены.

Ответ 3

Что бы я сделал, это столбец Identity в таблице EventVolunteer, который автоматически увеличивается с уникальным ограничением на пару EventId, VolunteerId. Используйте EventVolunteerId (идентификатор) в качестве внешнего ключа в таблице Shift. Это приводит к ограничению, которое вы хотите довольно просто, в то время как нормализация ваших данных несколько.

Я понимаю, что это не ответ на ваш общий вопрос, однако я бы счел это лучшим решением вашей конкретной проблемы.

Изменить:

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

Ответ 4

Есть способ сделать это, используя триггеры, которые я бы не рекомендовал. Я бы рекомендовал не размещать логику вашего бизнеса на уровне базы данных. ДБ не нужно знать, кто, укомплектовывает определенный сдвиг в это время. Эта логика должна быть помещена в ваш уровень buisness. Я бы рекомендовал использовать шаблон построения репозитория. У Скотта gutherie есть очень хорошая глава в его книге mvc 1.0, которая описывает это (ссылка ниже).

http://weblogs.asp.net/scottgu/archive/2009/03/10/free-asp-net-mvc-ebook-tutorial.aspx