Database code or application code.

[Andy B.]

I have some business rules that apply to some data in a database. I'm trying
to figure out if it should be put in the database or the application. Where
should I start?

 

[Lee Atkinson]

What kind of rules are you talking about?

 

[Andy B.]

I have an object that is quite complex. It is called Event. Here are some of
the rules I need to place.

1. An event can't be duplicated.
2. When adding new events to the system (sql server 2008) and a duplicate is
found, ask the user if they want to replace the current one with the new one
or just cancel out of the add process.
3. Events can't be scheduled on top of each other. The start/end dates and
times for the new event being added can't be in the range of existing
events. For example, If an event is scheduled for 3/1/2009 7:00pm - 3/1/2009
8:00pm, the new event can't start/end on that day between 7-8pm. For this
rule to work, the database would have to be scanned for all available/not
available times and then enforce the rule.

 

[sloan]

To me, some of these point to unique constraints.

Don't get me wrong I prefer business rule validation in the business layer,
not the database.

But these look like UNIQUE CONSTRAINTS.

You have to ask yourself, What makes an EVENT unique? The name? The name
and date?




if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Event]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [dbo].[Event]
END
GO

CREATE TABLE [dbo].[Event] (

[EventUUID] [uniqueidentifier] not null default NEWSEQUENTIALID() ,



EventName varchar(64) not null ,
EventDescription varchar(128) not null

)


GO

ALTER TABLE [dbo].[Event]
ADD CONSTRAINT PK_Event_EventUUID
PRIMARY KEY CLUSTERED (EventUUID)

GO



ALTER TABLE dbo.Event
ADD CONSTRAINT Event_EventName_UNIQUE UNIQUE (EventName)
GO




As far as the timeslots, you might look a "time lookup table" where you put
in 15 minute increments or something.
Time rules are especially difficult, especially "prevent overlapping".

I'd seek some more advice in this area.......

 

[lee atkinson]

Im doing something similar at the moment with events and I did most (if not
all the logic in .net) ... Partially because my sql isnt too hot , and also
I can create custom errors for things like duplicate events , entering
incorrect event date etc... Just seems easier to me to do stuff in classes

Maybe im doing everything wrong!

 

[Andy B.]

Don't feel bad. I kind of feel the same way. I did talk a little about the
subject a little in general on the sqlserver programming newsgroup and the
basis is that for smaller programs and stuff you can get away with things
like that. I figure if the security is good, what difference does it matter
anyways? I am writing a web application for a 2 member band and its 2 member
recording label. Considering on the fact that the venture isn't going any
larger than that, it would be considered small.