Re: Database Model - Class, objects and interaction

frebe wrote:
On Nov 22, 8:53 pm, Thomas Gagne <tga...@xxxxxxxxxxxxxxxxxx> wrote:
frebe wrote:
<snip>
Stored procedures should only be used as a last resort if you cant use
views to solve the problem. Abandoning a declarative approach in
favour of a imperative, is not a good idea.
That statements assumes a procedure is used in-place of a view. Stored
procedures can easily use views, and do many more things views can not. Even so, a view isn't nearly as valuable for defining an API, hiding
implementation, or loosely binding interfaces as procedures are.

In fact, a view is much more valuable. Lets say you want to select
invoices that are not payed. When you can create a view like this.

create view unpaid_invoices as
select invoiceid from invoice where amount > payed
I don't see the problem.

create proc unpaidInvoices as
select invoiceID from unpaid_invoices

I've not said that views shouldn't be created, only that they shouldn't be exposed as the interface to the database.
Next you want to have the number of days from current date to due date

create view late_days as
select invoiceid, datediff(currdate(), duedate) as late_days from
invoice

Next you can combined this predefined views (business rules) in
another query returning invoices that need to be reminded

create view reminders as
select invoicid
from unpaid_invoices ui
join late_days ld on ld.invoiceid=ui.invoiceid
where ld.late_days > 10

The problems with stored procedures is that they are not possible to
combine results in another relational expressions. If you had the
procedures unpaid_invoices and late_days, how would your reminders
procedure look like?
create proc reminders as
select invoiceID from reminders

What the procedure can do that views alone can not would be something like:

create proc reminders (IDENTITYREF @sessionID)
as
declare @status INTEGER

exec @status = hasPermissionFor
@sessionID = @sessionID,
@target = "reminder"

if @status /* whatever the definition of bad is */ BEGIN
exec logSecurityViolation
@sessionID = @sessionID,
@target = "reminder"

raiserror "You don't have permission for this query"
return
END

exec addInquiryHistory @sessionID = @sessionID
select invoiceID from reminders

return 0
Once you've created your problem domain's API to the DB you treat that
API just as you would any other API--you use it as the /only/
interface. Would you skip your windowing system's API and attempt
drawing and rendering directly to the screen to create menus, windows,
text and graphics? No. So why would you violate your domain's API and
read or scribble directly to the database?
The schema is the interface. Direct disk access is hidden far below.
It is better for an application to invoke a method/procedure to affect a
transaction than for the application to know all the places inside the
DB that must be updated to affect a transaction.

An even better solution would be to use check constraints or triggers.
Constraints and triggers are useful RI-enforcing tools, but databases are capable of doing more.
Be aware that the only responsibility for the database is to guarantee
that no invalid operations are performed, that might put the database
in an invalid state. The database is not responsible for storing every
scenario in which is may be used.
I'm not sure what you mean when you say, "The database is not responsible for storing every scenario in which it may be used."

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? Views are an excellent mechanism to automate joins and projections so that I needn't remember each table and the business-rules-compliant joins required for the same answers every time--and not only that I do the same thing every time (for both the application's benefit and my own) but that other humans using the system are relieved from knowing each table and the joins themselves so we're all using the same thing.

But business rules can be more complicated than simply defining overdue invoices. 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.
A schema is as much an interface as declaring all an object's data as
public--which is to say it is not an interface at all.

No. An DBMS hide everything about the internal implementation. For
example no disk access or index usage is exposed when using SQL. It is
on a completely different level than your C++ or Java objects.
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.

I differentiate between the interfaces. A DBMS interface is lower-level and more general than a system's interface (the transactions and rules that make your system different from mine) and which are higher level and less general, to the application interface and ultimately a graphical user interface which is much higher level and allows humans to do super-human operations that would otherwise be impossible.
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.

Imagine if your operating system didn't differentiate between system-level privileges and user-level privileges. Any application could start up, follow "the rules" as far as they thought necessary, and even though they didn't access the disk directly they may still be able to delete things, add things, edit files they shouldn't, and basically do anything that viruses and spyware are able to do--all without violating the implementation of the operating system--but ruining everything that was built on top of it.
In addition to integrity, using APIs, stored procedures, and
intention-revealing methods saves time in coding, unit testing, system
integration, and maintenance costs.

How?
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. 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. 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.

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.

Unit testing - I can unit test stored procedures by themselves--even before the applications that use them exist. All a domain's functions and transactions can be tested to be correct. With integrity already assured, higher-level unit tests can concentrate on fewer things specific to their purpose and implementation.

System integration - With domain-rules properly isolated from the applications that call them, they may be fixed or extended independent of their applications. Again, a basic feature and benefit of structured programming.

I should mention now that with domain system's integrity protected behind its API all the bugs introduced by applications are less-severe. In the last system I designed the errors introduced by applications--even the posting programs, were regarded more as cosmetic than anything else since the business rules were never violated.

Maintenance costs - Features may be either cosmetic (including usability) or systemic. Since the domain system is independent of its applications major features could be added through new tables, columns, views, and procedures, without impacting the rest of the system. Then, only the applications which interfaced with the contracts of the new features needed to be created or modified leaving the rest of the system untouched. The result was much less programmer time spent implementing new features than otherwise possible.

Again, another benefit of structured programming. All the benefits of structured programming about which books, pundits, consultants and others extol are available to all of us if only we considered our domain databases as more than just repositories of structured data.
When objects are properly designed they are created so their internal
representation is hidden from other objects, and others must use your
object's API (getters, setters, etc.) to interface with it. This
approach results in object models exhibiting loose coupling and strong
cohesion.
If you dont design your database properly (normalization), this might
be a necessary approach, otherwise not.
Your comment seems out-of-place. Either I don't understand your point
or you didn't understand mine. In this instance I was talking about
object oriented languages and the design of classes which I was using as
an analogy to support the argument that databases need the same respect
for interface as we give our objects.

Comparing a 3GL tools with a 4GL isn't very useful. In OO
encapsulation is used for solving problems that are already solved, by
separating the logical and physical models, in relational databases.
But a database can do more than just separate the logical and physical models, just as classes support more than C's structures do. Even without direct language support it is possible to implement the features of OO in a non-OO language. Many C APIs, like those for LDAP, iCal, or even database APIs like DBLib and CTlib are created so that the only (supported) interface to the data structures is through functions.
Your comment seems to suggest that if something was design properly in
the first place abstraction from its implementation wouldn't be
necessary.

First, a schema is not an implementation. Second, if you design it
well the use of stored procedures can be minimized.
Here we disagree. Just as the subclasses we create are more specific than the class we inherit from, database schema are a subclass tailored to be more specific than the DBMS they behave as metaclasses for, with each database region being an instance of the database described by its schema.

If thought of this way it would be difficult to argue that a class' data, if properly implemented, would require no methods to act on it.
What good is it to design something right the first time if
the business rules change?

If the rule for reminding invoices in my example above changes, you
just change the view "reminders". Everything else is unaffected.
Here we agree, but the reminders view is a straw-man of such simplicity that it's not a good candidate. If query were to become more complicated, requiring temporary tables or other processing that couldn't be expressed in the syntax of CREATE VIEW, then where would that leave application programs? Furthermore, a view may be a great mechanism for SELECTing data, but is much less useful for INSERTs and UPDATEs where the rules can easily demand more than constraints and triggers can provide.
Design and build your system like this and you'll find it assembles more
quickly and easily than other approaches. It will be easier to identify
bugs, enforce security, add auditing, and prototype new functionality
when the functionality can be created and tested first using the
database alone than it can when it can only be tested with both
application and database tightly coupled.
But above all, it will give you a lot of extra work. When you get
tired of writing new stored procedures for every possible SQL used,
you will start trying to reuse procedures that doesn't really fit.
That comments makes little sense. You have to write the SQL, write it
once, put it in a stored procedure, and use it multiple places rather
than re-writing the SQL for each place you may use it.

The problem is that you have to do it even if the SQL statement is
used only once, in which case you add extra work. If it is used
multiple times, create a view, which is much better tools for solving
the problem.
I prefer flexibility over time than convenience today. I am insufficiently omniscient to know if a SQL statement will only ever be used once. It's is one thing to use agile, but quite another to create it. Implementing domain interfaces as I've described using stored procedures to protect a system's integrity results in an entire system that is agile--that is--it can be extended very quickly in all respects than might otherwise be possible.

It is possible I might think differently about creating a system I'm only responsible for creating and not maintaining or improving, or a system upon which my future won't depend on its adaptability. The systems from which these rules were created were the bread-and-butter of the companies that owned them. Entire companies' futures depended the future of the software they were built around.

But regardless of how important any system may be, using basic principles of structured programming described by Wirth have tangible benefits that go beyond the mere structure of the code.
That's really no
different than structured programming. Do you not use subroutines in
your code?

I use views and constraints.
You don't program?
/frebe


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