请想象一下这个小数据库..。
图表
删除了失效的ImageShack链接-志愿者数据库关系图
表格
Volunteer Event Shift EventVolunteer
========= ===== ===== ==============
Id Id Id EventId
Name Name EventId VolunteerId
Email Location VolunteerId
Phone Day Description
Comment Description Start
End
联想
志愿者可以注册多个活动。
活动可以由多名志愿者组成。
一个事件可以有多个班次。
一个班次只属于一个事件。
一个班次只能有一个志愿者。
一名志愿者可以为多个班次工作。
检查约束
发布于 2010-04-07 05:21:51
加强数据完整性的最佳位置是数据库。请放心,一些开发人员,无论是有意还是无意,都会找到一种方法将不一致的东西偷偷放入数据库中,如果你允许他们的话!
下面是一个带有check约束的示例:
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)
)
发布于 2010-04-07 09:50:43
问题1很简单。只需让您的移位表直接引用EventVolunteer表,您就都设置好了
发布于 2010-04-07 05:30:09
我要做的是在EventVolunteer表上有一个自动递增的Identity列,并对EventId、VolunteerId对进行唯一约束。使用EventVolunteerId (标识)作为移位表的外键。这将强制执行您想要的约束,相当简单,同时在某种程度上规范化您的数据。
我知道这不是你一般问题的答案,但我认为这是你具体问题的最佳解决方案。
编辑:
我应该完整地读完这个问题。这个解决方案将防止一个志愿者在同一个活动中做两个班次,即使它们不重叠。也许将班次的开始和结束时间移动到EventVolunteer中,并在该表上对时间进行检查约束就足够了,但是这样您就可以在shift表之外获得移位数据,这对我来说并不直观。
https://stackoverflow.com/questions/2588072
复制相似问题