views:

50

answers:

4

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.

A: 

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.

Brian
+2  A: 

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

Sparky
Indeed thats a good solution and FYI, I already have a VIEW in place which 'obeys' the same. But I can't ignore data inconsistency so as a second half of this solution - I've to implement one of those 3 approaches
Hemant Tank
Not sure what you mean? Once you delete the SITE and ORG Relationship in the OrgSite table, the view will automatically reflect that? If I misunderstood something, please let me know...
Sparky
Of course the VIEW will have consistent data but what about the actual UserSite table? It'll still have 'orphan' records which I don't want. Because it can be dangerous in case that table is being referred separately
Hemant Tank
Why do you need to keep the UserSite table at all? If you create a view by the same name, any application code that SELECTs from it should not see any difference.
Sparky
+3  A: 

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.

Civil Disobedient
No data manipulations thru DB - its done from within the app
Hemant Tank
A: 

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.

rmalayter
Good Point!But no for now we don't have any hierarchy for User or Org so its flat.
Hemant Tank