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:
- 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?
- 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?
- 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!
- 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.