I'll try to present this somewhat neutrally. There's a long-standing argument about business rules and databases and in my quest to get this written before breakfast, I'm going to oversimplify like mad. Don't shoot me.
Business logic in the application:
Business logic in the database:
So my informal, useless survey is basically this:
Other thoughts, comments welcome.
How often have you ported an app from one database engine to another (e.g., MySQL to Oracle)?
How often have you had completely separate apps writing to the same data store (using completely different code bases)?
Do you depend on non-portable database features?
Have you experienced programmers damaging your data because the logic wasn't in the database layer?
You have to be careful whenever you use the word "all" or "always" when it comes to computing.:-)
Generally, the business logic that belongs in the database is usually "data rules", like "an order must have a customer", "a customer must have a name". Business logic that doesn't belong in the database are things like process flow logic. Generally.
Now on to your questions:
A few times: Informix to Sybase, proprietary to DB2, and SQL Server to Oracle.How often have you ported an app from one database engine to another (e.g., MySQL to Oracle)?
Writing, not often; reading, quite a lot. This is a typical pattern in data warehouses and reporting apps.How often have you had completely separate apps writing to the same data store (using completely different code bases)?
Yes. Unfortunately most databases don't fully comply with SQL standards yet.Do you depend on non-portable database features?
Depends on what you mean by "damage". I've had people generate garbage reports (and turn them over to management) because the database didn't enforce constraints properly.Have you experienced programmers damaging your data because the logic wasn't in the database layer?
Short answer -- If you have DBAs who earn their big bucks, do it their way. If not, ask why not; and do it so that your next project will be easier. Data logc belongs in the DB. Logic specific to a specific use of the data belongs in the specific application.
Stored Procs may hide the SQL from the programmers, but embedding the SQL in the 3GL hides the SQL from the DBAs who may be able to optimize the SQL. Same problem as embedding Perl in HTML or vice versa -- a content management system that lets you keep HTML templates so web designers can design 'em without breaking their template-ness and without breaking the embedded Perl (or Python with it's magic whitespace *shudder*) is easier for big projects. Same with big DB projects, you want to have the SQL where the experts can get at it without breaking the Perl or Java.
(ex-Oracle users keep asking IBM when they'll add HINTS to DB2. The answer always is that the DB2 optimizer doesn't need your hints, would only be thrown off by them; and if you want a feeble optimizer that needs to be micro-managed by hints so you feel important, you can go back to using Oracle. DB2 admins have better things to do, like optimizing the IOs to increase throughput, or adding an index where even the optimizer can't do better than a tablescan.)
Coding those non-portable Triggers, Stored Procs by hand will require adjustment on a later port. If that's what it takes, it's worth it. But using tools is better.
If using professional DBA tools, those are all auto-generated from the design drawing, porting is a snap. I haven't fully checked the capabilities of the FL/OSS DBA design tools, I don't know how much of the Trigger/StoredProc/RI feature set they support yet. I may need to check it out soon, since I need to port some data from MySQL to MySQL. It will be a hard port because the PHP WebSite schema changed between versions
ASIDE#1 -- The best database I ever used was WANG PACE. It claimed to be relational, but had some pretty OO features before there was a name for that other than "Post-Relational": It was really class/method oriented without the OO Jargon. The rich Data Dictionary cataloged Types for columns by type names, so natural joins could match the FirstName, LastName fields by type even if the column names were different. Types, columns, tables could all have stored procs (exits, methods) registered for and triggered on events. These "exit procs" could be coded in your choice of SQL, C/C++, or COBOL -- interoperably. Relational integrity was a given; a checkbox at on table-def says to reject add w/o parent or auto-create parent; another says to drop parent if last child deleted or not. With RI having Foreign Key declarations, it was trivial to generate default CRUD screens (Create, Retrieve, Update, Delete) with drill-detail, multiple lines of detail, etc. All on TERMCAP-style green-screens in the late 80's. It's taken 20 years for the industry to catch up, *sigh*
ASIDE#2 -- My Second favorite DB was IBM QBE, Query By Example, which iirc also had RI/FK. You built your query by sketching examples of records you wanted on T-diagrams of the table schemata on a 3270 green-screen, using constants in a column, variables to link joins, and a condition box to constrain relations. Elegant -- but really for on-line ad-hoc query, not for programming.
How often have you ported an app from one database engine to another (e.g., MySQL to Oracle)?
3 times from MySQL to PostgreSQL, but they were all small DBs. A couple times from Access to MS SQL but I'm not sure how much that counts.
I've never done this when there was more than app using the DB.
How often have you had completely separate apps writing to the same data store (using completely different code bases)?
Many, many times.
Do you depend on non-portable database features?
Always - but as little as is possible.
Have you experienced programmers damaging your data because the logic wasn't in the database layer?
Nope.
1. How often have you ported an app from one database engine to another (e.g., MySQL to Oracle)?
Once in 8 years.
2. How often have you had completely separate apps writing to the same data store (using completely different code bases)?
About a dozen (still maintained) projects.
3. Do you depend on non-portable database features?
It depends. Beyond default values, not nulls, primary keys, foreign keys, autoincrement and constraints (cascade delete...) I generally don't encode business logic in the database. Nevertheless even this isn't very portable.
I also use database functions such as "NOW()" and date formats which all seem to vary across databases, although I try to use standards where they exist.
4. Have you experienced programmers damaging your data because the logic wasn't in the database layer?
Never.
1. How often have you ported an app from one database engine to another (e.g., MySQL to Oracle)?
A fair number of times. Must be a dozen or so in the last 10 years. Once already this year and it's only February - and this was the second time that this particular application had it's database changed from underneath it.
2. How often have you had completely separate apps writing to the same data store (using completely different code bases)?
A significant minority of applications.
3. Do you depend on non-portable database features?
Often.
4. Have you experienced programmers damaging your data because the logic wasn't in the database layer?
Occasionally.
About as many times as I've had people damaging data by going around the logic in the database layer. Some dumb ass will always find a way to damage data if that's what they're trying to do
Personally I think the location of the business logic depends more on the application, the development team, how it's going to be supported, the size of the application, amount of development time, responsiveness of the DBA, etc. than on more abstract issues of technical merit.
If I've got an application that's talking to a large corporate database used by three other 24x7 applications written in a combination of four different development languages then I'm going to stick the business logic down in the database layer.
If I've got an intranet tagging tool that's being developed for a single department, a technology development path that means we're going to be switching databases in 18 months, rapidly changing requirements and a database department with a 15 working day turn around on change requests then I'm going to put the business logic in the application layer.
2. Whenever I have seperate applications writing to the database, they're pretty much always just seperate wrappers around a library - eg, a GUI front-end and a command-line front-end.
3. I try not to use DB-specific features, at least in low-end databases like SQLite and MySQL, on the (almost certainly spurious) grounds that I might upgrade later without re-writing the app. Writing overly-portable code is a bad habit of mine. I don't particularly mind using DB-specific features for high-end databases like Oracle though, as the probability of ever moving is so low. And anyway, you usually choose Oracle precisely for its proprietary features.
4. Yes, but the real reason was poor documentation. What they did was correct by the docs.
