Is it a best practise for a database to allow apps

Question

I've heard reasoning behind this and I was curious if others think of this as a best practise/good idea.

One reasoning is, that putting restrictions on direct access to database tables, and forcing apps/users to use SPs (Stored Procedures) to do CRUD operations will allow DBAs

1. fine grained control to approve/review SPs before moving them to production
2. avoid random queries written by developers which might cause server load/other server issues

This means the developer can not write Linq queries on tables (although Linq queries involving SPs are possible) This also means the developer has to forget about compile-time checks as well as being in total control of the data, rather principally use another language(SQL) to work with data.

I am not objecting to this, neither think this is a great idea. I am just curious to know what others think. What are any other advantages and disadvantages of this approach.

Update: As mentioned by Thomas in his answer, I tend to think that SPs are logic and using 'database programming in SQL' to do logic is okay as long as it's not 'Business logic'. Business logic needs separation, compile-time check and integration checkpoints and what not.

Answer 1

The database is a bucket, once you start treating as a part of your business logic you will end up in a very tight and uncomfortable place and create MUD instead of SW...

Separate logic and data. SPs are logic...!

Evil tongues (me that is) claim that SPs are database vendors techniques to create lock-in technologies which will make it harder to create abstractions that makes it easy to change database vendor...

SPs are in 99.9% of their uses an ANTI-design pattern...!

Answer 2

Depends entirely on your environment. The answer to the question really isn't a coding problem, but a business decision.

If your database supports just one application, and is reasonably tightly integrated with it, then it's better, for reasons of flexibility, to place your logic inside your application program. Under these circumstances handling the database simply as a plain data repository using common functionality looses you little and gains flexibility - with vendors, implementation, deployment and much else.

On the other hand if your are handling a corporate database then it is highly advisable to screw down the security as far as you can. At the very least all appropriate constraints should enabled, and if possible access to the data should be through views and procedures only. Whining programmers should be ignored in these cases as...

1. With a corporate database the asset is valuable and invalid data or actions can have business-threatening consequences. In these circumstances your primary concern is the business, not how convenient access is for your coders.
2. Such databases are by definition accessed by more than one application. You need to use the abstraction that stored procs offer so the database can be changed when application A is upgraded and you don't have the resource to upgrade application B.
3. Similarly the encapsulation of business logic in SPs rather than in application code allows changes to such logic to be implemented across the business more easily and reliably than if such logic is embedded in application code. For example if a tax calculation changes it's less work, and more robust, if the calculation has to be changed in one SP than multiple applications. The rule of thumb here is that the business rule should be implemented at the closest point to the data where it is unique - so if you have a specialist application then the logic for that app can be implemented in that app, but logic more widely applicable to the business should be implemented in SPs.

Answer 3

It depends massively on your data and your environment.

If you're making your database widely available to disparate readers and writers, use SPROCs: that way you maintain absolute control.

If you're in more of a closed shop where you trust the developers, use LINQ. It allows much greater flexibility of access to the data and makes future changes to the database a breeze. (E.g. if you add a field to a table you just have to regenerate your LINQ data access partial classes and you're done; otherwise you'll have to rewrite a bunch of SPROCs, as well as altering your data access layer in your application.)

Answer 4

Yes, particularly in Oracle.

In Oracle, triggers and foreign keys require many SQL/PLSQL context switches.

I personally develop databases that require large CRUD operations in the following way:

1. I use neither triggers nor foreign keys.
   • No modify permission on tables are given to the users.
   • All DML in the database are performed this way:
     1. Data to be inserted/updated/deleted are loaded into the TEMP table
   • A stored procedure is called, in which:
     1. Data are checked for consistency as in BEFORE I/U/D triggers and foreign keys
        • Data are MERGE'd into the table
        • Data are checked for consistency as in AFTER I/U/D triggers and foreign keys

In the database I have a service table that holds these foreign key-like relations between fields.

The stubs for the procedures are generated automatically from this table, and need to be recreated every time the table changes.

All checks are performed by a single query that processes thousands of rows, instead of calling the trigger in a loop thousand times.

This is a huge increase to modification performance.

If you need to UPDATE, say, 50,000 rows in a 10,000,000 rows table, this may take 10 seconds instead of 200 seconds of even more.

---

Of course, it pays for itself only if you need really fast response to really large operations.

It's very difficult to design a database this way, and it's prone to errors if you're not used to it.

Answer 5

Looks like you don't trust the developers to write well-behaved queries.

This is probably from experience.

I would suggest that if bad developers are writing crap sql code, they are also writing bad application code, and that means management is doing a poor job.

The problem isn't the dynamic SQL (even if parameterized) versus the stored procedures: it's poor management.

That's not going to be solved by forcing db access through stored procedures.

Anyway, even if access to the DB is only provided through stored procedures, bad coders will still be able to stress the database.

The reality is that if you make data hard to get to in the database, developers will use other techniques to not put the data in the database, or perverting the database. For example, say you have a blog field to store pdfs or something, what's to stop them from serializing objects and storing them in the blob, to be retrieved and de-serialized in code?

I would be careful on that.

Make sure that the stored procedures end up in source control, and that deployment of stored procedures to the appropriate database environment can be performed automatically by the build tool (continuous or daily)...

Answer 6

I work on a team that employs this approach.

There are other benefits, such as

1. Security - if you don't have direct access to the base table, it can be part of a defense in depth approach.
2. Flexibility in your PDM - you can make largescale changes to your PDM for performance reasons, and as long as you preserve your 'contracts' (SPs, Views), the application layer is unaware.
3. Futureproofing - You're well positioned for another application, possibly written in another language, to come along and access your database.

It works best when you have a dedicated database person or team.

I am watching this area with interest, as it definitely seems to run against the techniques required when using ORMs.