August 12, 2009 at 8:53 pm
Hi All,
I am designing a small database about Tools Management in our school project.
I just want to hear about other comments on how to specifically implement this rule.
I have this tables:
Table: tblStudent
Columns: StudentID,StudentFName,StudentLName
Desc: Holds data about students
Primary Key: StudentID
Table: tblTools
Columns: ToolID,ToolDesc
Desc: Holds data about tools
Primary Key: ToolID
Table: tblToolsTrans
Columns: StudentID,ToolID,CheckoutDate,CheckinDate
Desc: Holds data about tools being borrowed
Primary Key: StudentID,ToolID,CheckoutDate
Basically, a student is only allowed to checkout a maximum of 3 tools. I guess
this how they want this to be implemented. But I just want to ask how to implement this on the database?
Is it possible to implement this using database constraint or should I just let my application check first the number of records in the tblToolsTrans table for a particular student with CheckinDate still null.
Kindly advise which is the better way. Thank you and hoping for your reply even though my question might be simple. I just started out designing database on my own. 😀
August 13, 2009 at 6:41 am
Personally, I'd recommend both. Put the constraint in the database to prevent accidental violation of the rules, and put code into the front-end that checks on the rules and neatly prevents the issue from arising. You can't be too paranoid about your data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 13, 2009 at 7:17 am
Never rely on just an application to maintain the integrity of your database. Other applications (and DBAs!!) may also modify your data.
If there's nothing in the database to enforce the business rules, then you can be fairly certain that, at some point, you will end up with data that violates those rules.
What usually happens at that point is that the application expecting the data to conform to those rules just falls in a heap with it's legs in the air.
August 14, 2009 at 4:08 am
Mr. Newbie (8/12/2009)
Is it possible to implement this using database constraint or should I just let my application check first the number of records in the tblToolsTrans table for a particular student with CheckinDate still null.
SQL Server unfortunately has quite poor support for referential integrity and other declarative constraints in the database. Such rules often have to be implemented in procedural code either in a database trigger or in application / middle tier code outside the database or in an external business rules engine.
There are plenty of advantages to implementing business rules inside the database or as close the database as possible. I would consider using a stored procedure or trigger for your example. If the rule needs to be configurable (maximum could be N instead of 3) then that's probably a good reason to put it in external code - preferably in a service tier or other server-side code.
August 14, 2009 at 9:49 am
> Basically, a student is only allowed to checkout a maximum of 3 tools.
Over the course of their lifetime, or 3 tools at any given point in time?
August 14, 2009 at 9:55 am
David Portas (8/14/2009)[hr
SQL Server unfortunately has quite poor support for referential integrity and other declarative constraints in the database. Such rules often have to be implemented in procedural code either in a database trigger or in application / middle tier code outside the database or in an external business rules engine.
huh?
are we talking about the business rule, to limit 3 child items per record in the master table, or in general? the limit can still be done with a table level constraint and a function to count child rows...as the original poster envisioned...I'm confused on your statement.
Lowell
August 14, 2009 at 10:18 am
here's a simple example I whipped up to show the table constraint preventing more than three items checked out per student id:
Create Table tblStudent (
StudentID int identity(1,1) primary key,
StudentFName varchar(60),
StudentLName varchar(60) )
Create Table tblTools (
ToolID int identity(1,1) primary key,
ToolDesc varchar(60))
Create Table tblToolsTrans (
StudentID int references tblStudent(StudentID),
ToolID int references tblTools(ToolID),
CheckoutDate datetime default GETDATE(),
CheckinDate datetime)
GO
CREATE FUNCTION dbo.CheckToolCount(@StudentID int)
returns int
AS
BEGIN
DECLARE @Results int
SELECT
@Results = COUNT(*)
FROM tblToolsTrans
WHERE StudentId = @StudentID
AND CheckinDate IS NULL
Return @Results
END
GO
--add the constraint to prevent multipel checkouts
Alter Table tblToolsTrans ADD CONSTRAINT CK_NotToManyTools CHECK (dbo.CheckToolCount( StudentID) <=3)
--proof
Insert Into tblStudent
SELECT 'My','Name'
insert Into tblTools
SELECT 'Microscope' UNION ALL
SELECT 'Drafting Kit' UNION ALL
SELECT 'Calculus Kit' UNION ALL
SELECT 'Physics Kit' UNION ALL
SELECT 'Engineering Kit'
--
INSERT INTO tblToolsTrans(StudentID,ToolID)
SELECT 1,1 UNION ALL
SELECT 1,2 UNION ALL
SELECT 1,3
--"return" the microscope
UPDATE tblToolsTrans
SET CheckinDate = GETDATE()
WHERE StudentID = 1
AND ToolID = 1
--check out TWO Tools, the seconf tool should fail, as that would be the 4th tool
INSERT INTO tblToolsTrans(StudentID,ToolID)
SELECT 1,4
INSERT INTO tblToolsTrans(StudentID,ToolID)
SELECT 1,5
Msg 547, Level 16, State 0, Line 3
The INSERT statement conflicted with the CHECK constraint "CK_NotToManyTools". The conflict occurred in database "SandBox", table "dbo.tblToolsTrans", column 'StudentID'.
Lowell
August 14, 2009 at 10:29 am
I was talking about both the specific case and the more general situation.
Using a function as part of a constraint is a procedural solution rather than a declarative one. It's a very flawed approach and if you aren't extermely careful (or lucky) then sooner or later something will break. Here's a very simple example:
CREATE TABLE tbl (id INT NOT NULL PRIMARY KEY, x INT NOT NULL);
GO
CREATE FUNCTION dbo.fnRowCount(@x INT)
RETURNS INT
AS
BEGIN
RETURN (SELECT COUNT(*) cnt FROM tbl WHERE x = @x);
END;
GO
ALTER TABLE tbl ADD CONSTRAINT chk1 CHECK (dbo.fnRowCount(x)<=3 /* No more than 3 rows for x */);
GO
INSERT INTO tbl (id,x) VALUES (1,1);
INSERT INTO tbl (id,x) VALUES (2,1);
INSERT INTO tbl (id,x) VALUES (3,1);
INSERT INTO tbl (id,x) VALUES (4,2);
GO
UPDATE tbl SET x = x + 1;
Running the UPDATE will likely cause an error ("The UPDATE statement conflicted with the CHECK constraint") even though the constraint should never have been violated. Worse still, this result isn't even reliable - sometimes it works and sometimes it doesn't! That's no solution for implementing a business rule in my opinion.
Generally speaking declarative integrity support is limited in SQL Server. To a large extent this is just a consequence of being based on SQL, which lacks the ability to do mutliple assignment and therefore has limited scope for referential integrity and other multi-table constraints. Some other DBMSs have "deferrable" constraints as a way of supporting more complex multi-table business rules but SQL Server doesn't support deferrable constraints.
August 14, 2009 at 10:33 am
Lowell,
I posted before I saw your reply but I think you will find your solution suffers the same problem.
August 14, 2009 at 10:40 am
your example was interesting!
I see how that fails, when I'd obviously think that was jsut fine...That's what you mean about having to test the business condition in the trigger instead, right...because of this kind of situation.
I know about deferable contraints of course. Thanks for the example and explanation.
Lowell
August 14, 2009 at 10:42 am
David Portas (8/14/2009)
I was talking about both the specific case and the more general situation.Using a function as part of a constraint is a procedural solution rather than a declarative one. It's a very flawed approach and if you aren't extermely careful (or lucky) then sooner or later something will break. Here's a very simple example:
CREATE TABLE tbl (id INT NOT NULL PRIMARY KEY, x INT NOT NULL);
GO
CREATE FUNCTION dbo.fnRowCount(@x INT)
RETURNS INT
AS
BEGIN
RETURN (SELECT COUNT(*) cnt FROM tbl WHERE x = @x);
END;
GO
ALTER TABLE tbl ADD CONSTRAINT chk1 CHECK (dbo.fnRowCount(x)<=3 /* No more than 3 rows for x */);
GO
INSERT INTO tbl (id,x) VALUES (1,1);
INSERT INTO tbl (id,x) VALUES (2,1);
INSERT INTO tbl (id,x) VALUES (3,1);
INSERT INTO tbl (id,x) VALUES (4,2);
GO
UPDATE tbl SET x = x + 1;
Running the UPDATE will likely cause an error ("The UPDATE statement conflicted with the CHECK constraint") even though the constraint should never have been violated. Worse still, this result isn't even reliable - sometimes it works and sometimes it doesn't! That's no solution for implementing a business rule in my opinion.
Generally speaking declarative integrity support is limited in SQL Server. To a large extent this is just a consequence of being based on SQL, which lacks the ability to do mutliple assignment and therefore has limited scope for referential integrity and other multi-table constraints. Some other DBMSs have "deferrable" constraints as a way of supporting more complex multi-table business rules but SQL Server doesn't support deferrable constraints.
David - your test is flawed. At no point does your data exceed 3 rows per value of x. ALL values just move up (in batch, so "at the same time") by one in the X column.
that said if you were to fix your update to be:
UPDATE tbl SET x = x + 1
where x=1;
(which would cause x = 2 to violate)
You will find that the constraint DOES fire.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 14, 2009 at 10:50 am
Matt, you missed the point of what I was saying. My UPDATE should not violate the constraint, that's true. But what I wanted to demonstrate was that the constraint will generate an error sometimes even when the constraint is not violated.
August 14, 2009 at 10:59 am
why does it fail though? I'm speculating that the constraint is merging the INSERTED and the DELETED rows to get the count or [INSERTED] and [tbl], so it finds a count of 4 where x=2 (3 from INSERTED,1 from DELETED(or[tbl])
and thus raised the error?
Lowell
August 14, 2009 at 11:03 am
David Portas (8/14/2009)
Matt, you missed the point of what I was saying. My UPDATE should not violate the constraint, that's true. But what I wanted to demonstrate was that the constraint will generate an error sometimes even when the constraint is not violated.
and my point is - your script does NOT show that. In the original state - there is no error. Nor should there be.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 14, 2009 at 11:03 am
David Portas (8/14/2009)
...Using a function as part of a constraint is a procedural solution rather than a declarative one. ...
Conceptually and in principle, Functions, especially "true" functions (no-side effects) are counted on the Declarative side of the house, not the Procedural.
And while SQL Server's implementation of scalar functions does allow them to be written in a procedural way, Lowell's example and application would count as declarative by any objective standards that I know of.
That's not to say that relational or SQL purist's wouldn't have a problem with SQL Servers approach in this regard, I just don't think that it can fairly be called "procedural" in this case.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply