Re: Navigation question

From: Carl Federl <cfederl_at_yahoo.com>
Date: 14 Feb 2007 12:22:59 -0800
Message-ID: <1171484579.648015.63160@a75g2000cwd.googlegroups.com>

<Do large, production-quality, highly usable and useful, data-based,
read-and-write software applications actually exist where there is no code in the software that navigates around the database? >
Yes, there are many large, production-quality, highly usable and useful that do not have any database navigation performed by the application.
>
Picture a data entry screen with header information about a company and a list of orders presented to the user. Are applications written where the exclusive approach would be to read all of the data in and write the data back using a single view?> Typically not using views, although that is possible, but instead persistent stored modules (ISO term) are used. SQL DBMS vendors have implemented three type of persistent stored modules:
Stored procedures that be invoked from an application Triggers that automatically run when a table action occurs (insert, update or delete)
User-derfined function that can be referenced in a SQL statement but cannot be called by the application.

There are also been a lot of success with using XML both as output and input with stored procedures as in this example:

create view CustomerOrderProducts as

SELECT 	Customers.CustomerID
,	Customers.CompanyName
,	Orders.OrderDate
,	Orders.ShippedDate
,	OrderProducts.OrderID
, 	OrderProducts.ProductId
,	Products.ProductName
, 	OrderProducts.UnitPrice
,    	OrderProducts.Quantity
,	OrderProducts.DiscountPct
FROM 	Customers
join	Orders
	on  Orders.CustomerID = Customers.CustomerID
join 	OrderProducts
	on OrderProducts.OrderID = Orders.OrderID
join	Products
	on Products.ProductId = OrderProducts.ProductId

go
create procedure CustomerOrderProducts_CustomerId ( @CustomerId nvarchar(4)) as
select * from CustomerOrderProducts
where CustomerId = @CustomerId
order by Customers.CustomerID , OrderProducts.OrderID , OrderProducts.ProductId
as XML AUTO
go
create procedure CustomerOrderProducts_OrderId (@OrderID integer) as
select * from CustomerOrderProducts
order by Customers.CustomerID , OrderProducts.OrderID , OrderProducts.ProductId
as XML AUTO
go
The output of the CustomerOrderProducts_OrderId for orderId = 10248 is:

<Customers CustomerID="VINET" CompanyName="Vins et alcools Chevalier">
<Orders OrderDate="1996-07-04T00:00:00"

ShippedDate="1996-07-16T00:00:00">
<OrderProducts OrderID="10248" ProductId="11" UnitPrice="14.0000"
Quantity="12">
<Products ProductName="Queso Cabrales" DiscountPct="0.0000000e+000"/></
OrderProducts>
<OrderProducts OrderID="10248" ProductId="42" UnitPrice="9.8000"
Quantity="10">
<Products ProductName="Singaporean Hokkien Fried Mee"
DiscountPct="0.0000000e+000"/></OrderProducts>
<OrderProducts OrderID="10248" ProductId="72" UnitPrice="34.8000"
Quantity="5"><Products ProductName="Mozzarella di Giovanni" DiscountPct="0.0000000e+000"/></OrderProducts>
</Orders></Customers>

As you can see, there is no application navigation of the database.

For inputs, an application can accept messages, convert to XML and then provide XML to a stored procedure which will: Convert the XML into virtual relations
Perform the appropriate Insert, Updates and Deletes to the underlying tables - in this example, Customers, Orders and OrderProducts Received on Wed Feb 14 2007 - 14:22:59 CST