Re: Database Model - Class, objects and interaction

frebe wrote:
<snip>

So on top of every view you also have to create a procedure? That is
extra work.
No, I was just proving stored procedures can call views. Since our Model Programming Interface (MPI) is implemented in procedures, the only time we use views is when procedures share common SELECTs.
<snip>

Your example shows very well how ugly and messy your approach is. You
have to mix high-level business logic with low-level infrastructure
stuff like security and logging. Security is best handled by the
database security system. Views are an excellent helper her too.
Logging is best done triggers. If these mechanisms are not can't help
you, then a stored procedure might be an option to solve the problem.
So given 100,000 users, you would create database accounts for each? You would also depend on the DBMS' security for implementing your application's security?
Application systems should be developed from the bottom up. Each layer
of a system is responsible for accommodating humans' limitations. First
is what we can remember--hence a database. One I've a database I'm only
limited by my SQL skill at what I can record or extract from it. The
problem with SQL, from a human standpoint, is how can we be sure the
same SQL is used over and over so we may guarantee the same results?

Use views. (How can we be sure that the same SP is used over and over
again).
Discipline, design, development standards, refactoring, pair programming, all kinds of good stuff that are common with professional, experienced programmers following shop rules.

<http://it.toolbox.com/blogs/anything-worth-doing/production-rules-as-easy-as-1-2-3but-without-2-and-3-16577>
But business rules can be more complicated than simply defining overdue
invoices.

Yes, and view can be more complicated than my example.
That, of course, depends on the DBMS. We were using Sybase, and Sybase's views are not as flexible as their procedures. Of course, we can't use the results of a procedure in another JOIN, but like any limitation we simply avoided it or worked around it. Ultimately, that specific limitation was the least of our concern.
Business rules may also include who can ask the question,
when the question may be asked, or whether an audit trail of the
question must be maintained.

This AOP issues are better handled separate. It would be much better
having some kind of request handler managing audit trail, than
implementing it in every SP. Current DBMSes are not very good at AOP,
so I would handle it in my application.
Part of our system's integrity was its reliability, and reliability is often assisted by simplicity. Creating separate logging or auditing processes means someone could have tried "fixing" something under the covers by calling the "right" procedures--and not be audited or avoid security.

Many of our programs were SQL scripts. Scripts were used for daily processing, fixes, and upgrades. Each of these scripts were fully compliant with the system's security mechanisms which meant they were logged which meant they could be audited.

There were two major advantages for us. First, whenever our technology was investigated during due diligences or other technical reviews we received high marks. Also, whenever our finances were audited they also received high marks, and were done quickly and at lower expense.
<snip>
A DBMS is responsible for hiding the particulars of physical IO and
organization from the user (DBA). But once the DBA creates a database
for a specific application domain it is the DBA's responsibility to hide
the particulars of how their application-specific database design
implements its lower-level functions from the next higher
level--applications.

Clever, by making almost the entire application as SPs, the DBA has to
do it instead you. That is why you doing notice the extra work.
Our developers were our DBAs. We created an artificial barrier between development and production support, but not between developers and DBAs. Same folks. I think treating DBA as a role rather than a resource contributed to the system's agility. No more fights between development and DBAs about how something should be implemented. Saves a lot of time.
Having a map is
not the same as having directions. A schema is a map showing all the
places you might go and ways you might take to get there, but an API
knows the best, safest, and supported routes so business rules and data
integrity are preserved.
Data integrity is mainly enforced by using constraints in a
declarative way. Stored procedures are a last resort when everything
else fails.
If your only concern is referential integrity I may agree. If you're
concern is for system integrity--which is a higher-level integrity, then
I prefer to invest in stored procedures rather than each application
programmer's following the rules, or my ability to sufficiently describe
them to programmers or enforce them.

The better you design the schema, the smaller the difference between
referential and "system" integrity, will be.
You either have a high opinion of schema or low requirements for system requirements and integrity to believe the space between them can ever be narrow.
<snip>
Saves time in coding - since procedures can be more sophisticated than
views (as demonstrated above) an application programmer need only invoke
the procedure to affect all its transactions.

A stored procedure called directly from the client is nothing more
than an extension of the application hosted in the database. The
statement above applies to functions in your application as well.
In addition to "protect the database" we had a couple other rules (not as important, though). The first was that an application should do one thing and do it well. We also believed applications should be more responsible for "why" and "when" to do things, but not "how" some things should be done. It is my experience that HOWs, WHYs, and WHENs have different change rates. Any opportunity a designer has to separate those three concerns is to the system's advantage.
Since stored procedures
are programming-language neutral it matters not whether the application
is written in COBOL, C, or Java, or is being rewritten in another
language--the business rules remain unchanged and the integrity of the
system is maintained.

Stored procedures are written in programming-languages as PL/SQL, T-
SQL. They are VERY vendor dependent. Using a lot of SP makes it very
hard to change DBMS vendor.
Nice straw man. The decision to change DBMS' is not the same as which language to use. Our shop was multilingual. Primarily PHP and Smalltalk. We were considering there might be compelling reasons to create some LISP applications as well.

I realize you can't argue against it (because how could you know), but I've seen more systems migrate from one language to another (FORTRAN IV, FORTRAN 77, C or COBOL to JAVA) than I have from one DBMS to another (which I have seen).
If the rules change it is easier to load a new
stored procedure than change the code of all the applications to reflect
the new rule change.

This is an deployment issue. For example an web-application written in
PHP or ASP doesn't have to be reloaded. Only the changed script need
to be updated.
You correct in that deployment has a lot to do with it, but so does separation of concerns, how many applications may be affected by the change, etc. The smaller the system the more likely fewer things will be affected. The numbers and types of affected things is also influenced by the size and complexity of the change.
For anyone familiar with structured or object-oriented programming,
these are the same advantages to creating a subroutine or class so that
functionality exists once and only once.

So what is the advantage of writing an SP instead of a subroutine or a
view?
If you can't list at least five reasons by now I've wasted your time.

--
Visit <http://it.toolbox.com/blogs/anything-worth-doing> to read my rants on technology and the finance and consulting industries.
.

Relevant Pages

  • Re: Storing Connection String
    ... if the credentials you use for your application can only execute ... SSPI security is more expensive to ... My idea is to have just one database user whose username and password ... As per database I would use only stored procedures to ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Database Model - Class, objects and interaction
    ... The problems with stored procedures is that they are not possible to ... is what we can remember--hence a database. ... If your only concern is referential integrity I may agree. ... principles of structured programming described by Wirth have tangible ...
    (comp.object)
  • Re: POD speed
    ... >> It does not provide data integrity, security and access. ... > not be hard to implement in a POD. ... Database integrity involves much more than transactions, ...
    (comp.lang.java.databases)
  • Re: Error SceCli 1005
    ... that was also a SceCli event ... Security policies were propagated with warning. ... Some JET database is corrupt. ... Initiating INTEGRITY mode... ...
    (microsoft.public.windows.server.active_directory)
  • Re: Users Logins
    ... database - objects such as tables, views, stored procedures, ... If you go into SQL Server books online, ... Managing Security. ...
    (microsoft.public.sqlserver.security)