Re: Database Model - Class, objects and interaction

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

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?

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

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.

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.

In addition to integrity, using APIs, stored procedures, and
intention-revealing methods saves time in coding, unit testing, system
integration, and maintenance costs.

How?

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.

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.

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.

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.

 That's really no
different than structured programming.  Do you not use subroutines in
your code?

I use views and constraints.

/frebe
.

Relevant Pages

  • Re: Serious errors with Create view command
    ... the database is opened? ... There's no database on earth that behaves that way! ... What on EARTH does the option of 'Edit Stored procedures' do? ... I can't paste it INTO the view designer sql view ...
    (microsoft.public.fox.helpwanted)
  • Re: Business objects, subset of collection
    ... SQL only works when the statements are ... all items (invoices) are subscribing for events. ... all items and all items has to execute the criteria evaluation, ... The features of a OO database is basically the same as of a network ...
    (comp.object)
  • Re: Transaction Oriented Architecture (TOA)
    ... OOP is not required to wrap SQL. ... of stored procedures to act as an API for application programmers ... writing code against the database. ... With a well-defined API in place, ...
    (comp.object)
  • Re: Get all stored procedures
    ... For SQLSERVER, we can use the following SQL statement to query the SP ... objects of a certain database: ... Microsoft Online Support ... | Thread-Topic: Get all stored procedures ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Cannot Open SQL Server Table in Access.ADP File.
    ... I dont think that SQL Server will take 'veiw dependencies' into effect; ... Generate Script Wizard did not work on my original database, ... After fixing some of my tables and a few stored procedures and views, ...
    (microsoft.public.access.adp.sqlserver)
Loading