Stored Procedures, ORMs and other application layers

Question

I'm just starting out on a project to develop a new, fairly substantial web application which has an underlying MSSQL database. We're hiring a team of developers to write the application (in .NET) and I'm building the database (I've actually already started).

My initial plan was to write Stored Procedures in my database to handle all of the business logic, retrieving and updating data and so forth, and have the developers just make calls, passing the appropriate parameters. Then, because there's a strong chance we'll want to create mobile apps which replicate some parts of the web app's functionality, I thought we'd create a middle layer of web services to interact with the database and serve the applications, but I was still going to have this middle layer call Stored Procedures.

I've spent most of the past few days reading up on ORMs and reading the arguments for and against using them instead of SProcs or as well SProcs, or not at all. It's a minefield, and I'm really looking for some clear guidance on how I should be structuring this project. I know there's the potential for discussion here but I'm hoping to avoid that by asking some clear questions:

1. As I understand it, an ORM will basically automatically generate SQL to perform the tasks that the developer needs, e.g. creating a new record, deleting a record, etc. I can see how that works for basic data manipulation, but what if things are a bit more complex? What if, for example, I don't actually want any records deleting from the database but, rather, I just want a record to be flagged as 'deleted' when a user deletes it so that it is hidden in the application but still present in the table? Presumably, an ORM would just generate code to simply delete the record and actually remove it, and calls to retrieve records from that table would include records flagged as 'deleted' unless the developer knew the underlying data structure well enough to know to exclude these. By contrast, I can just write a SProc called 'DeleteRecord', which flags the specified record as 'deleted' and another called 'GetRecord' which only returns records not flagged as 'deleted', and when the developer uses these they don't need to know what's actually happening in the database. And this is just a simple example - some of my SProcs were going to do several things in response to a seemingly simple request (e.g. writing to an Audit table when a record is modified). How would an ORM handle this sort of thing?
2. The only reason for me creating a middle layer between the application and the database is so that future apps, particularly mobile apps, can access the data using the same method. My idea was to create web services which would retrieve data from the SProcs and return it in XML or JSON, as well as providing services for creating, updating and deleting. Does this seem reasonable?
3. Reading online about layered application development, recommendations vary from two to about eight different layers! Am I just being naïve when I wonder whether it's possible to just end up going down a 'layer black hole' in all of this? Our application will be used by hundreds, not thousands, and will contain hundreds of thousands (maybe eventually millions) of records in its key tables, not billions. I can see the benefits of a degree of abstraction, but I prefer to keep things as simple as I can at the same time!
4. If our hired developers arrive and I tell them that they're accessing the database using purely SProc calls and not an ORM, are they likely to be comfortable with this? Are they likely to look at me as if I'm from the dark ages? Would they be justified in thinking that we were going about this the wrong way?

There are probably lots more questions, I'm just having trouble articulating the utter confusion and slightly depressing sinking feeling that comes from having thought I had it all worked out and now wondering if I'm in way over my head.

Answer 1

I'm assuming that you're using .NET for development since you mentioned MSSQL as your database but my recommendations apply to O/RMs available for other platforms as well.

CRUD Soft Deletes and Auditing

For your first issue, CRUD operations and filtering of "soft deletes". The O/RM itself provides a foundation that allows for more complex scenarios to be achieved. Many developers use the patterns and principles of Domain Driven Design when leveraging an O/RM.

One pattern that will help in your scenario is the Repository pattern. The idea of the repository pattern is that it acts like a collection of your Entities (objects that are backed by your database). You can pass it filters (in .NET LINQ expressions) and it returns the objects that match your filter. The beauty of the .NET implementation is that when you back it with Entity Framework or NHibernate, the LINQ expressions get converted to SQL and executed on the database rather than in memory.

"So how does this help me with soft deletes?" you ask.

Your repository can provide a filter that is appended to every query by default. And calling delete on your repository can be implemented by setting the "IsDeleted" property on the Entity rather than deleting it from the DB.

Audit tables can be implemented by triggers if you must. Or for a more advanced solution, there is the Event Sourcing design. With Event Sourcing, you keep a running tally of events that happen in the application. So for instance if you want to find out why the price on a product in your inventory changed, you can look at the event history and see when and why it was changed as well as by whom.

Exposing Services

Once you have created your Domain Model, there is nothing preventing you from creating a wrapper around it to provide Use-case driven access to the Model. This is called the Application Service Layer. It provides a simplified interface to your domain model so that your application can remain shielded from changes that don't directly impact it.

Layers

People get obsessed with complicated architectures...to me there are three simple elements to an application UI, Business Logic, Storage. You might decide to break them up into different conceptual layers (e.g. your Mobile UI has to access a hosted service around your business logic) but for the most part it's splitting hairs.

What Is Business Logic?

This was difficult concept for me to grasp at first when I moved from a database-centric mindset to Domain-Driven Design. The business logic lives in your entities. Which are mapped to the database.

The difference between just mapping classes to tables/objects to rows and Domain-Driven Design, is that DDD Entities are rich Objects in the Object-Oriented sense. One of the principles of Object-Oriented programming is encapsulation, that is your data and the logic surrounding that data are linked together in a class.

Take for example your rule that a Client must have at least one Address. In DDD lingo, this would be called an invariant. Basically, the Domain would not allow you to construct a Client without all of its invariants being valid. What would that look like? You'd have a constructor for your Client entity that takes an address and any other information that is mandatory for a valid Client. There are a few good blog posts about the concept (this one gives a good discussion with links going into further details).

Long story short, the business logic goes with the data that you're storing.

Developers

All of it's subjective. More important than how you do storage is your development methodology, how you get information to you developers what to build. Overall architectural design, how do the various pieces of the solution work together. Once you have a clear direction laid out for these things, how you do storage shouldn't matter.

Final Note

One thing that I don't like about Stored procedures is that you have to duplicate your logic. You write the sproc in the database, then you have to write the code that interacts with the sproc and translates the results into your objects. It's tedious work (and error prone) let the O/RM handle that if you can.

Answer 2

First of all no matter what you choose you want your business logic outside your database as much as possible. There are many reasons for this, the top couple being you get tied to your database so if you need to change to Oracle or SQL Server it becomes a much harder task, SQL isn't designed to handle business logic gracefully which results in massive stored procedures and functions that could have been a few lines of code instead.

Also you will want a layer that handles all the data access between your app and database no matter if you choose an ORM or stored procedures. One reason for this is so it could potentially be used for multiple apps, it also makes your app less reliant on the actual database making changing that easier as well.

As for deciding whether to use an ORM or not, there is likely nothing in you app that would expose a weakness of an ORM. Your concern about hard vs. soft deletion isn't an issue, the steps are basically the same either way, you put an update statement in the DeleteRecord procedure, or you change the deleted property in the record object in the DeleteRecord method you create. The shortfall of ORMs is generally always performance, and they have been steadily improving on that front. Orms start lsoing when you start getting close to Big Data territory (millions of rows and maybe even tens of millions) or you are at extreme levels of traffic (Amazon, HFT).

There are developers that are comfortable with either style, that said ORMs are more popular now and you will definitely have a bigger talent pool willing to work with an ORM, this is not necessarily a better talent pool.

Answer 3

Have you considered hiring your developers and then letting them decide what architecture to use? By doing this you will ensure that you aren't excluding developers from the selection process simply because they don't like the architecture you've chosen. Getting the best possible team of developers is the most important thing for ensuring your project is successful, and with more to choose from you may find better developers that you're happier working with.

People are more important than architecture.

Answer 4

You want to build a database with all the accessibility code and business logic and then hire developers to build some sort of user interface. You're taking on a lot of the development responsibility here and not giving the developers enough room to do their job. Is there some reason you don't feel you're going to hire competent people who can help you figure all this out?

Here are answers to your questions:

1. Either the people you hire know how to follow the requirements using an ORM or they don't. You call it delete, but it's really an update to the flagged/soft deleted record.
2. Middle Layers not only allow other apps to share the code, but it accomplishes a separation of concerns. If a new developer wants to know where the business logic is, they'll probably look to the middle layer, but in your case, they're going to be surprised to find it in your database.
3. I don't multiple layers/abstractions is about handling large data sets. It's about maintainability as well. It's just assumed that large data sets are more complex, but that's not always the case.
4. Not devs will agree, but if you do choose stored procs over an orm, you better know what you're doing since you're handling the business logic. They may have had bad experiences with a DBA who did not. Also, can you keep up with their demands or will you be a bottleneck. In your absence, they're stuck with working with procs, so they may not like it in the long run.

Answer 5

1. Call delete method, which sets the delete flag and updates the object. ORMs have some impressive functionality beyound simple CRUD operations. And they still allow SQL as a fall back.

2. If by 'the application' you mean the UI, then the real reason for the middle layer is so you don't have a 'Smart UI' design which becomes harder and harder to maintain and improve. Instead, you have now seperated out concerns. The ability to add a new front end is one of the benefits of seperating concerns.

3. Stick with 3 for starting. Data persistance (aka, database). Data model. User interface. Going beyond that at the start, unless you really know what you are doing, is overengineering it.

4. This depends upon why you are telling them. For a brand new, not yet built system, if you weren't upfront with this and you are laying down the law, it will be taken as micromanagement and will scare off the developers who have other options (and those are the ones you most want to keep). If you say no ORM because we have a production enterprise application without one and it'll be too much work to add it in, but you remain open to being convinced otherwise down the road, it'll be taken better.

If you plan to hire devs from the start, get them and have them help make these calls. If you want to get a product out by yourself and are worried about building it up once it becomes a hit and you'll need a bigger team, file that away under 'problems I want to have' and pick what ever you will be most likely to get a working project with.