SQL Server DB & App design level suggestion needed. Constraint in DB or App?

Question

I need some suggestions to implement a business rule - whether to keep it in DB (using TRIGGERs) or in App code.

--- Table structure:---

# ORG - Master table for Organizations 
# USER - Master table for Users (each user belongs to an Org so there's a field OrgId which is FK ro ORG) 
# SITE - Master table for Sites

# ORGSITE - {OrgId, SiteId} links Site(s) with Org(s) 
# USERSITE - {UserId, SiteId} links Site(s) with User(s)

The constraint is that : "A site is accessible to a User ONLY if its accessible to his Organization."

---

Now, it happens in the app that on day1 we relate Site1 to Org1 and then we're able to relate Site1 to User1 (User1 belongs to Org1). On day2 I delete the relationship between Site1 & Org1 from ORGSITE (this requires that I also delete the corresponding User1 & Site1 relationship from the USERSITE table).

This is handled from within the app code. So, now my question is where shud I keep the above constraint handling -

APPROACH#1:

Deploy TRIGGERs on the ORGSITE table and USER table which will handle the activity for:

1. On after delete for ORGSITE (delete corresponding USERSITE records)

2. Onafter update for USER (if User's Org is changed then delete all his records from USERSITE)

APPROACH#2:

Handle everything from within the code - tap the events which trigger those DB actions and delete records from USERSITE (as and when necessary). Need to manage via a Transaction.

APPROACH#3:

Simply, add a new field OrgSiteId in the USERSITE table which is an FK ref to an 'Auto Increment PK: Id' of ORGSITE. Next, I'll deploy the cascaded delete for the USERSITE.OrgSiteId FK. This will handle most of the things and make it implicit!

Hope I explaind well. Is APPROACH#3 really gonna work? If not - what is your preference and why?

Thank you for your time.

Answer 1

Hey,

You could use triggers; that is a viable option that would work. I personally would not use cascading deletes as that can be dangerous.

I tend to prefer the code option, because it allows me specific error handling, logging, and reference checking. SUre you have some of that in triggers, but that's my personal preference. Plus that puts the logic with your other application changing code, so all of the logic is in one place, and not broken out into application/database. But again, that's a personal preference for me; triggers are a perfectly good and viable option.

HTH.

Answer 2

I would suggest:

• Drop the UserSite table

• Create a view called UserSite

  SELECT a.UserId,b.SiteID FROM User a LEFT JOIN b.OrgSite ON b.orgid=A.Org

This will return the sites the user can see, or NULL if he or she can't see any

Answer 3

If you think someone would ever run a query straight from the DB (someone like, say, a DBA) then you should handle it in a trigger. If you do it through the app, you have to always do it through the app.

Answer 4

Are the orgs heirarchical? Is there an ORG.parent_org_id field?

If so, your problem is a bit harder, because you likely need a person to be able to see all SITEs tied to their org or their org's children (sort of like a folder permissions in a file system).

The view solution from Sparky above could still work in this case if your database supports recursive JOINs or common table expressions. The ANSI-standard way to do that is with CTEs (only supported by SQL 2005 and later and PostgreSQL I think). Oracle and other DBs have non-standard syntax for the same recursion functionality.