tags:

views:

81

answers:

3

A table can only store 10 records in a particular state, 10 users over 30 years for example, the others must be less than 30 years. It is a business rule and as such should be respected. How to ensure that state? Think: multiple users accessing this table.

Explicit Locking?

A: 

Either make some ugly database procedure (and be hated forever by whoever is forced to maintain it), or simply enforce the rule in the bussiness layer (probably in some "storeUser(User user)" method). If you have many different places in the code from where you are storing users, then this will be a valuable lesson in architecture design :)

Robert
If there are many different technologies that may need to apply this rule (.NET, java, php), putting the logic in a database procedure may be the way to go because it only needs to be maintained in one place and can be optimized by a database expert.
RussellH
Let's forget the procedure. Even in the application, I need to request the blocking of the table, right? Only this will ensure the table integrity. Do you agree?
Thomas
Yes, just put the validation and storing in single transaction, that will do it.
Robert
Care is needed when dealing with table locks because it involves questions about performance and scalability, so I wanted to confirm with more belief. Thanks guys.
Thomas
-1 for bashing database layer enforcement of data integrity. Often this is the best place for this logic, and would be appreciated by maintenance developers not hated.
Randy
Well, I am a maintenance developer and I hate it. All bussiness logic should be in one place, the bussiness layer. Bussiness logic in DB destroys portability and layer separation - clearly an antipattern. I don't deny that it may be neccessary sometimes, as all antipatterns are.
Robert
Again, let's forget the procedure. The main objective of the question is to confirm the blockade of the table. A business layer will have to make this block before performing validation.Thanks for the answers!
Thomas
A: 

This requires a business layer method for create and update operations. read and delete can be allowed. Those synchronized add and create methods implement the business rule and respond with an error status.

Andreas_D
I agree with you. Thanks.
Thomas
I agree with this. However (correct me if I'm wrong) it won't work for an app deployed in more than one server, because the lock will be performed for each virtual machine.
bloparod
Bloparod, the blockade would be on a table in a single database and not about the java object. So have multiple virtual machines is not a problem.
Thomas
A: 

SIRA_PRISE allows you to just declare that constraint in algebraic form, and SIRA_PRISE will enforce your constraint for you without you having to write any code what so ever.

Or, second-best option, you can write triggered code in some SQL system and hope you haven't made any mistakes/overlooked anything, but your chances for that are not so good. "Applied Mathematics for database professionals" has a whole chapter devoted to this very problem. Be in for quite a few aha-erlebnisses.

Or, third-best option, you can write application code to enforce your constraint and hope no one will ever make another application doing updates to your database and overlook the constraint (the chances of which are pretty high, because, honestly, where is the appropriate place that you can document the existence, let alone the formal specification, of your constraint ?). I haven't seen any great facilities for that in neither ER nor UML.

Erwin Smout
The way that ensures the integrity of a data set must be unique. Means that any application that wants to change that set of data should use THAT way. Only one application will modify a data set DIRECTLY.I see no problems in applying the restrictions in the application.Thanks for the answer Erwin.
Thomas