RE: SQL Syntax -- MIDRANGE-L

> From: jpcarr@tredegar.com
>
> SO,   Respectfully, - watching a user do his own query over DB2/400
> straight into
> an Excel spreadsheet and then do a pivot table and produce a graph just
> before
> a management meeting - I would have to disagree and stand by my original
> position that the data belongs to the USER.

And using an interface layer (such as a stored procedure), they could do
this just fine and never even know where the data was stored - on the
network, much less the disk.  USERS DON'T NEED ACCESS TO THE DATABASE.

I guess I'm just incapable of explaining myself.  The user certainly owns
the data.  But they don't own the database.  It's my responsibility to make
sure they can get at the data, but they shouldn't have to care how it's
stored physically.

Let's take an example.  In BPCS, the item master stores available inventory
using four fields: opening balance, receipts, issues and adjustments.  In
most cases, users don't care about the individual fields, they just care
about the current available inventory.  They shouldn't have to know how the
data is stored on the disk to do that, but in your world they do.

And what if I decide I need to change how the data is stored on disk?  All
of a sudden the user has to change EVERY QUERY THEY WROTE.  This is a
maintenance nightmare waiting to happen, and a very serious breach of
responsibility on the part of the programmer.  Those wonderful happy users
you talk about would have your head if you changed how the data was stored
on your machine.

Or say you need to merge with another corporation that uses a different data
format.  Now the user has to modify their query to understand both the old
format and the new format, and once again update EVERY QUERY.

You think this is a good thing?  If so, you and I view the job of IT very
differently.  I think you're just not willing to do your job.

Because there's no reason you can't provide a flexible information layer
that would allow your users to access the data, yet still hide the
implementation from them.  This concept - hiding the implementation - is so
universally accepted today that this insistence on direct SQL access to raw
data boggles my mind.  It's completely opposed to every good programming
practice we've been fighting to put in place over the years.

What would it take to implement this tier?  Simple.  All access to the
database is through stored procedures, and the user only has access to the
elements that are available from those stored procedures.

Where do these elements come from?  Create a catalog of all relevant
business data.  And I'm going to go on a limb here and use the actual
singular form, datum.  Each datum would represent a business entity, such as
a customer number or an available quantity.  These definitions for each
datum would be stored one level higher than the actual database.  You would
assign the security and access rules at this level.

Below that would be MY level, the level of the programmer.  I would
determine how each datum related to physical fields on the disk.  A single
business data could conceivably cross rows, tables, and machines, but their
storage would be completely transparent to the user.  As long as I
guaranteed the access to those business data, the users would be perfectly
happy.  The only time users need access to raw data is if I haven't been
responsive enough to their needs.

And if, for some reason, the users needed access to some particular piece of
information that wasn't cataloged, we'd simply add a new catalog entry and
update the affected stored procedure correspondingly.

Is this feasible today?  In the world of stored procedures, it is most
assuredly possible.  However, it's work.  It's easier and faster to simply
give access to the user, and future changes be damned.

Short term, giving access to the database may solve some of YOUR problems as
the programmer, but long term, you're creating an environment that has
potentially disastrous side effects.

It's a business decision, I suppose, but given the potential damage, it's
unlikely you can make a real business case for direct user access, even read
only.  But you may disagree.  If so, more power to you.  I just pity you
when you have to change your database.  Because your users aren't going to
be so happy then.

Joe