Business Rules & Referential Integrity

[Paul Johnson]

Good Day All.

I have been trying to find general answers to a very specific question about
application design and implementation using DotNet.

My situation is that I would like to design and implement a Web Application.

I would like to avoid using datasets in favour of the datareader to
development time, increased application effeciency and a number of reasons
mentioned in the MSDN Library entitled "Recommendations for Data Access
Strategies".

Making this decision seems to pretty much end any ideas of creating a
distributed application.

I was hoping to implement the business logic and referential integrity in
stored procedures at the database. A quick example would be the following:-

CREATE PROCEDURE dbo.InsertItem
(
@IID int = NULL OUTPUT,
@Item_ITID int = NULL,
@Item_IBID int = NULL,
@ItemModel varchar(50) = NULL,
@ItemColour varchar(50) = NULL,
@ItemSerialNo varchar(50) = NULL,
@ItemBarCode Image = NULL,
@ItemDatePurchased datetime = NULL,
@ItemPrice money = NULL,
@ItemVAT money = NULL,
@Item_IRID int = NULL,
@ItemValue money = NULL,
@ItemImg Image = NULL,
@ItemImgPath varchar(255) = NULL,
@Item_IGID int = NULL,
@Item_IIID int = NULL,
@ItemNotes varchar(255) = NULL,
@Item_SPID int = NULL,
@Item_SLID int = NULL,
@Item_TXID int = NULL
)
AS

--- @msgnum = (nn=DBID(10),nnn=TID(001),nn=OPID,nnn=ERRID)
SET NOCOUNT OFF;
DECLARE @return_status int

--- CHECK Is Item Type Valid?
EXECUTE @return_status = Exists_ItemType @Item_ITID
IF @return_status <> 1
raiserror(1001002001, 16, 1)

--- CHECK Is Item Brand Valid?
EXECUTE @return_status = Exists_ItemBrand @Item_IBID
IF @return_status <> 1
raiserror(1001002002, 16, 1)

--- CHECK Is Item Receipt Valid - NOT MANDATORY?
IF ISNULL(@Item_IRID, 0) <> 0 BEGIN
EXECUTE @return_status = Exists_ItemReceipt @Item_IRID
IF @return_status <> 1
raiserror(1001002003, 16, 1)
END

--- CHECK Is Item Guarantee Valid - NOT MANDATORY?
IF ISNULL(@Item_IGID, 0) <> 0 BEGIN
EXECUTE @return_status = Exists_ItemGuarantee @Item_IGID
IF @return_status <> 1
raiserror(1001002004, 16, 1)
END

--- CHECK Is Item Insurance Valid - NOT MANDATORY?
IF ISNULL(@Item_IIID, 0) <> 0 BEGIN
EXECUTE @return_status = Exists_ItemInsurance @Item_IIID
IF @return_status <> 1
raiserror(1001002005, 16, 1)
END


INSERT INTO Item
(Item_ITID, Item_IBID, ItemModel, ItemColour,
ItemSerialNo, ItemDatePurchased, ItemPrice, ItemVAT, Item_IRID, ItemValue,
Item_IGID,
Item_IIID, ItemNotes, Item_SPID, Item_SLID,
Item_TXID, AO)
VALUES (@Item_ITID, @Item_IBID, @ItemModel, @ItemColour, @ItemSerialNo,
@ItemDatePurchased, @ItemPrice, @ItemVAT, @Item_IRID, @ItemValue,
@Item_IGID, @Item_IIID, @ItemNotes, @Item_SPID,
@Item_SLID, @Item_TXID, 2)
SET @return_status = @@ERROR
IF @return_status <> 0
raiserror(1001002006, 16, 1, @return_status)
ELSE
SELECT @IID = SCOPE_IDENTITY()

GO


The above procedure would attempt to ensure business logic and referetial
integrity by using other stored procedures.
Any integrity checking that fails would result in a user defined error
number and message being raised and the exception caught within the Web
Application.

1) Does this design/approach seem appropriate or is it a normalpractice?
2) I understand that it is possible for the integrity to still be
compromised. Should I also implement Declarative Referential Integrity by
using relationships and foreign key constraints. Is this normal practice?
3) Attempting to code referential integrity allows me to raise specific
errors that can easily be interpreted and handled by my application however
a foreign key constraint error seems impossible for me to interpret and give
any positive feedback to the user.

I hope I have mase myself clear and someone could shed some light on these
processes.

Kind Regards
Paul Johnson.