Architectural Dilema ?? Membership driven data logic RRS feed

  • Question

  • I have a fairly straight forward question regarding any architectural patterns related to membership driven data logic (thats my recently coined phrase so I hope it doesn't mean something else somewhere).

    The context is as follows:
    - My opnion is that Data Replication is bad
    - Database code should contain as little business logic as possible
    - Application maintainability and scalability is paramount
    - We have about 500 queries per second (just to give you an idea of size)

    The problem:
    - The applications handles IT incidents in the workplace. These are also referred to as problems / issues
    - The applications thousands of users who are organised into both groups and roles.
    - The Roles users have in groups allow them to access certain areas of the application e.g. Reports
    - The groups users belong to allow them to see the incidents that are currently assigned to them.
    - Users can only view incidents assigned to groups they themselves belong to.
    - The Framework database holds all groups and users and replcates a simple table into the Incidnet database.

    To select a list of tickets for user I need to scan the groups a user is in to get the tickets assigned to their groups for display on the front end. e.g.

    SELECT *
    FROM tblTickets t
         INNER JOIN tblTicketAssignment ta ON t.ticketId = ta.ticketId
    WHERE ta.groupId IN (SELECT groupId from userGroupList WHERE userId = @userId)

    Therefore all the stored procedures are tied to a specific user and all sps have the username passed into them. I have thought about other options such as passing the list of groups in but some users are members of 1000+ groups so that doesn't work.

    I feel I am mixing Business Logic in the data base. I feel that the BLL doesn't know anything about these rules so can't enforce them. I need to use replication just to get a single list.

    - If there a data pattern example for this concept. All examples I come across seem to display everything all the time.
    - By using the rules in the database sps do I break having CRUD only stored procedures.
    - Does anyone have any good examples / suggestions for alternatives. We are talking about a fairly large, but simple, application. Selecting all tickets and then filtering in the business logic would result on about 500,000 rows returned to the front end to get a list of 20 for me the user.
    -When passing a user around in applications should I use the IIdentity of the thread. My web based applications use application account to access the database not user accounts. Many of my users don't have domain accounts.

    Hope its not too confusing. If you need any clarifications please post and I'll try.






    Saturday, March 24, 2007 3:02 PM

All replies

  • Hi Scott,

    One option you have it to move the SQL closer to the  business logic. I see are using Stored procedures - while stored procedures are sometimes better moving SQL close to the BL is not that much of a problem and is sometimes better (see for example http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx ). I said closer to the BL and not part of the BL intentionally as I personally think it is better to separate it from the code itself. To do that  you can use a tool like iBatis.net or an O/R mapping tool (see you can see a paper I wrote on OR mapping here:  http://www.rgoarchitects.com/Files/ormappin.pdf)

    Also - From "Selecting all tickets and then filtering in the business logic would result on about 500,000 rows returned to the front end to get a list of 20 for me the user" it seems to me you don't have an applicaion server  in the middle between your clients and database. While it requires careful planning - it will enable you to have higher scalability (compared with 2-tier client/server) for example you could introduce caching in this tier for  incidents or membership or both - as well as have finer control on update transactions etc.

    Hope this helps,


    Monday, March 26, 2007 5:12 PM
  • Sometimes it is necessary to do data intensive operations on SQL. The approach I use is to have a method in the business layer that calls the DAL which in turn calls the required sp.

    I call the method a very meaningful name and add a comment to both the method and the sp explaining the business logic there.

    There is no pattern that I know of that will achieve the same performance as moving the data intensive stuff to SQL.

    Tuesday, March 27, 2007 2:55 PM