Application Design with a Shared Database

Question

I have a generic design question that relates to enterprise applications but I think that software developers from other types of projects may have experience with this as well.

The scenario is following - you have a database and you are about to write an application that provides access to its data. The database also contains data about user accounts, their roles and authorization. The problem is that other processes are accessing the database as well and any one of these processes may change any data at any moment (e.g., delete a user).

In other projects I used to have exclusive access to the database and so I usually wrapped the database with a layer that exposed the related domain functionality in a SOA-style. This wrapper had a rich domain model and could utilize caching of the database data because it was aware of any potential change (by intercepting domain events, CQRS) that could modify it.

With a shared database I don't know how to approach the design. Imagine this situation:

1. HTTP request arrives to you application and you are to authenticate this request.
2. You query the database for the claimed user account and its login data. If the supplied credential matches then a session is established (e.g., in ASP.NET a secure cookie is sent to the client).
3. But then a subsequent request from that user arrives. The problem is that instead of doing something useful at this moment, it seems like that one needs to check whether the associated user account still exists in the database. Any information can stop being valid at any point because some other process may alter the database.

This is of course a more generic problem. The data may not only change between requests but even between two separate database calls. The easy answer here is to use locks/transactions. But the truth is that this would require huge changes to the database schema. For example, every user account would require an additional column 'InUse' - but that is just a very simple scenario. I can imagine that completely new tables would need to be created. Also, the transactions would typically span across many HTTP requests and thus render the database useless for long periods of time for other users.

I have read the PoEAA book and I am well aware of various synchronization techniques. What I am looking for here is some generic technique or methodology how to achieve the so called "good-enough-design" that does not require changes to the database schema and can handle the mentioned situations where anything can change between two requests.

Right now I think I will go with the following approach - I will authenticate the user. Then I will not perform any subsequent checks to ensure that he was not deleted in the meanwhile. But when I perform some operation that relies on the existence of the user I will receive an exception and deduce that the user must have been deleted. The response will then be some kind of error message or error page.

I am sure this is a common problem that many of you must have encountered. I will appreciate any solution or even a reference to some book or paper. Thanks.

Answer 1

If this is for an enterprise, I am surprised that you don't have a single sign on facility (or several) to use. That would be my suggestion. In my experience, it is not common to have applications sharing a database, even for authentication. The thing that gets me here is that it is another enterprise system reinventing the wheel with user authentication, another set of passwords to remember, etc.

Authentication is typically provided either by a web service call (SOAP), or using SSO, and sometimes using a message queue.

By having a separate database you are enforcing a separation of concerns, and you can keep your data to yourself. My suggestion would be if at all possible (which it's not always due to enterprise bureaucracy) to expose the user account management as a service.

If thats not possible and you do have to work with the constraint of the existing database, I would suggest your domain model includes the authentication tables.

In terms of dealing with data that has been changed, the easiest solution will probably to be maintain a session variable such as LoggedInDateTime that caches the value of DateTime.Now (assuming .NET and SQL Server, adjust as necessary), and have an update stamp on your user rows that have a trigger set to automatically update the stamp on an update. Then, for each request check whether the UpdateStamp has been updated since you logged in.

In terms of checking if the user is deleted, per request check that the user row still exists (if using physical deletion) otherwise check for a DeleteStamp or IsDeleted value.