Re: theory and practice: ying and yang

"Paul" <paul_at_test.com> wrote in message news:429c728b$0$40245$ed2e19e4_at_ptn-nntp-reader04.plus.net...
> mountain man wrote:
>>> Even if the DBMS package is bundled with a graphical frontend (e.g.
>>> Enterprise Manager for SQL Server), it's logically separate from
>>> the DBMS itself.
>>
>> Yes, the tool (GUI) is separate from the DBMS and provides generic
>> connection to it. IOW, the same tool (GUI) is used for all different
>> industries, because it contains nothing but the ability to present
>> data sets.
>
> OK, so are you saying that all database usage could be done through a
> generic client, such as Enterprise Manager for SQL Server?

That's right. Not Enterprise Manager itself, but a much smaller program that essentially displays arrays of data, each being generated by the execution of stored procs.

The first procedure is the menu procedure, which examines the work group associated with the user, and provides a subset of the available applications from a table that serves as the application (or stored proc) register.

The stored procedures are created by DBA's or delegated SQL experts using Query Analyser or EM, external to the tool. Deployment is by a simple registration process to the tool.

> What about
> database that power websites?

I have a version of the tool written in .asp. Essentially it works the same way.

>What about operating systems that don't
> have that client available to them? I guess your argument would be that
> porting a single generic client to multiple platforms would be simpler
> than maintaining millions of different clients?

That's the aim, reducing the number of moving parts.

For a number of decades I have had to manage the change management of reasonably large software suites, integrated and modular, altogether hundreds/thousands of program objects.

I dreamed of replacing all program objects with one object that would not require change, and so this tool was created that deals with stored procedures alone.

> Also, the way a database is presented to an end-user for data input or
> output is often very different to a standard grid format.

While this may be true, a "bare bones" approach to information is services remarkably well by the standard array.

>You may want
> dropdowns or pick lists,

It does have a prompt box, the user-supplied values being passed into the stored procedure. (EG: Enter date "as at" for the debtor trial balance.)

> or to bring together data from several
> different tables (but not in a tabular form).

Data can be amalgamated from different tables, even different databases, and presented as req'd.

>You may want a first
> drop-down to narrow down the range of a second dropdown or something.

This can be achieved by drill-down convention. Choose level one TYPE.
Choose level two SUBTYPE.

> Maybe include graphics or boilerplate text. All this would have to be
> done on the client.

The tool has analytical capabilities (user can sort/filter dataset) but purposefully little else. The solution I use to generate often extremely meaningful graphs, is to design a stored procedure in which the explicit data underlying that meaningful graph is returned to the tool.

The user exports the data to excel and pushes the graph button. There's the graph. No frills, but nothing on the client.

...[trim]...

>> Both sprocs and views are essentially select statement. Perhaps our
>> preferences are simply based on past use?
>
> Stored procedures can be just select statements, but they can also be a
> series of SQL statements, SELECT, UPDATE, INSERT, DELETE, etc. to be
> executed in a specific order. And maybe wrapped in transactions that can
> be rolled back or committed.
>
>>>OK, that sounds reasonable. But don't you still have the problem that if
>>>the table designs change, you have to coordinate the corresponding
>>>changes in all the stored procedures that access those tables? Granted,
>>>they're now both in the same language, SQL, but it's still an issue.
>>
>> If the design were the adding of one column to a table, all
>> stored procedures using that table would not require change.
>> Only the sp's that need to include or make reference to this
>> new column would need to be altered.
>
> OK, but I'm thinking if you do something more drastic like split a table
> into parts (maybe to allow for multiple phone numbers or something), or
> change a column's data type. You could use views to get round some of
> these issues (especially on the reporting side) but because your
> underlying predicates have changed, your stored procedures will need
> rewriting. And in this scenario would it be that much more work to make
> the similar changes to a more remote client?

Definitely, because you may indeed have hundreds or thousands of dependent objects on a remote client, and each change needs to be coordinated between:

a) the database itself.
b) the client code changes
c) the server-side code changes.

Coordination issues may not seem much, but they exemplify the maxim that the sum is more than the parts, and sometimes very much more than the parts, depending upon complexity.

>>>How can SQL talk to a remote machine to draw a window on the screen and
>>>populate it with controls? Surely it requires drivers to enable clients
>>>to perform these functions?
>>
>> The tool is a window on a screen expecting to be populated
>> by a generic dataset (the result set obtained from the execution
>> of a stored procedure).
>
> So how does this differ from a chopped-down version of a standard
> database management tool like Enterprise Manager for SQL Server?

It doesn't really, except from the end users perspective, all the stored procedures are pre-defined by the DBA or some other parties. The user has a menu of such tasks available, and is not free to wander around.

Requests for enhancements of old tasks (reports, etc) or new applications are referred to a "developer" (eg: the DBA or SQL coder). Seeing as though the development is creating one or a series of stored procs and then registering them by name in the app register, the development cycle is exceedingly rapid.

>> The user on the remote machine talks to the tool (by a
>> simple line reference of an array, the clicking of a mouse
>> on the summary report line that says "Widgets").
>>
>> The tool sends this info to the RDBMS along with the
>> fact that a second level report has been invoked.
>>
>> The RDBMS executes the second level stored procedure
>> *using* the info supplied (in this case "Widgets").
>>
>> The RDBMS sends back to the tool (GUI) the result
>> set, and the tool displays this to the user.
>>
>> The user is able to (multiple) sort, or filter upon, this
>> result set, and/or print preview it, and/or export it to
>> various industry standard formats.
>
> OK, I see. This seems a bit low-level for a lot of users though; they
> don't want their clients to look like database tables - they want
> something less technical-looking.

I am from the old school. Information is the key, not the attractiveness of the presentation. While I may not win points on presentation, points would be won from anything else in industry for:

1. Substantially greater application response time & performance
2. Substantially reduced application development turn-around times:
3. ZERO Desktop Change Management considerations
4. Greatly enhanced simplicity of solutions:
5. Sophisticated versatile solutions cater for all organisational complexity:

>>>I don't quite understand what you mean by "drill-down" and I can't find
>>>references to this on your site.
>>
>> Here is an example Summary Level:
>> http://www.mountainman.com.au/software/southwind/southwind_menu_01_task_101_Sales_Analysis_by_Client_Level_1_of_2_CEO.JPG
>>
>> The user selects to view the detail for "Hungry Owl", on the above
>> screen by double-clicking that row. As a result, the details are shown:
>> http://www.mountainman.com.au/software/southwind/southwind_menu_01_task_101_Sales_Analysis_by_Client_Level_2_of_2_CEO.JPG
>
> That looks quite nice. How does the database designer define what is the
> behaviour when a user double-clicks on a given row?

The database designer is not necessarily required. We need someone to design a simple stored procedure making a select statement from one or many databases.

In the above example, it is the SQL coder (of the proc) who deliberately codes the proc as a report, with column 1, through to the last column.

By convention, the tool examines the first column of the row selected by the user, and passes this to the next level (ie: another pre-defined stored procedure *using* the value found in col1)

>What if you had more
> than one possible drilldown for a row?

Either (I prefer the first)
a) design a second application consistent of two stored procedures. The first procedure is already written, and the second might present other info for the client.

b) Have two rows per client, made unique by an identifier such as A = Drilldown into client sales, or B = Drilldown into client credit control correspondence.

>>>I think views are nicer from the end-user point of view, but I suspect
>>>they are a lot more difficult to implement from the point of view of the
>>>DBMS designer, being declarative rather than procedural.
>>
>> create view TEST_VIEW
>> as
>> select * from table
>>
>> create procedure TEST_PROC
>> as
>> select * from table
>>
>> How is one declarative and
>> the other procedural?
>
> In this case, they're both essentially declarative. But in general a
> stored procedure will have more than one SQL statement in it, using
> whatever procedural extension to SQL is used by that DBMS. (e.g. T-SQL
> for SQL Server). And this is giving the DBMS a sequence of steps to
> follow, and maybe some loops as well, so is procedural.

Thanks for the clarification. I try and keep all the procedures very simple, and most would therefore probably be regarded as declarative.

Codd (i have been doing some background reading) was always one to try and keep things as simple as possible.

> With views, you can have views upon views upon views, and I would guess
> that very deeply nested views are quite difficult to optimize,
> especially when things like aggregates enter the frame.

Well, I have very little experience with views, having been able to do without them all these years, so I cannot speak for them.

Stored procedures (and chains or series of them) seem to handle all manner of aggregates very well, and I dont seem to have problems in this area, or in performance.

-- 
Pete Brown
IT Managers & Engineers
Falls Creek
Australia
www.mountainman.com.au/software