sql-server - una - relacionar tablas en sql server por codigo

¿Cómo creo una restricción de comprobación de varias tablas?

Question

Por favor, imagine esta pequeña base de datos ...

Diagrama

se eliminó el enlace de ImageShack muerto - diagrama de base de datos de voluntarios

Mesas

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

Asociaciones

Los voluntarios pueden inscribirse para múltiples eventos.
Los eventos pueden ser atendidos por múltiples voluntarios.

Un evento puede tener múltiples turnos.
Un cambio pertenece a un solo evento.

Un turno puede ser atendido por un solo voluntario.
Un voluntario puede ocupar varios turnos.

Comprobar restricciones

1. ¿Puedo crear una restricción de verificación para imponer que ningún turno esté atendido por un voluntario que no esté registrado para el evento de ese turno?

2. ¿Puedo crear una restricción de verificación para imponer que dos turnos superpuestos nunca sean atendidos por el mismo voluntario?

Answer 1

El mejor lugar para imponer la integridad de los datos es la base de datos. ¡Tenga la seguridad de que algún desarrollador, intencionalmente o no, encontrará una manera de introducir elementos inconsistentes en la base de datos si los deja!

Aquí hay un ejemplo con restricciones de verificación:

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

Aquí hay algunas pruebas para las nuevas verificaciones de integridad de datos:

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

Aquí están las definiciones de la tabla:

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

Answer 2

Hay una forma de hacerlo mediante el uso de desencadenadores, que no recomendaría. Recomendaría no poner su lógica de negocios en el nivel de la base de datos. La db no necesita saber quién, es el personal de un cierto turno en qué momento. Esa lógica debe ser puesta en su capa de negocios. Yo recomendaría usar un patrón de construcción de repositorio. Scott Gutherie tiene un muy buen capítulo en su libro de mvc 1.0 que describe esto (enlace a continuación).

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

Answer 3

La pregunta 1 es fácil. Simplemente haga que su tabla Shift se refiera directamente a la tabla EventVolunteer y ya está todo listo.

Answer 4

Lo que haría sería tener una columna de identidad en la tabla EventVolunteer que se incremente automáticamente, con una restricción única en el par EventId, VolunteerId. Utilice el EventVolunteerId (identidad) como la clave externa a la tabla Shift. Esto impone la restricción que le gustaría de manera bastante simple, mientras normaliza sus datos un poco.

Entiendo que esta no es la respuesta a su pregunta general, sin embargo, considero que esta es la mejor solución para su problema específico.

Editar:

Debería haber leído la pregunta completamente. Esta solución evitará que un voluntario realice dos turnos en el mismo evento, incluso si no se superponen. Tal vez sería suficiente trasladar las horas de inicio y finalización del turno al EventVolunteer y tener la restricción de verificación en los tiempos en esa tabla, aunque entonces tiene datos de cambio fuera de la tabla de cambios que no me suenan intuitivos.