|
|
Posted by David Portas on 03/22/06 11:27
Herb Caudill wrote:
> Maybe the problem here is that I want SQL to be something more than it
> is. As an object-oriented programmer, I expect to be able to encapsulate
> logic at the lowest possible level. In this situation, "logic" includes
> sorting logic. The conclusion on this thread is that there's no way to
> do that - that in a chain of nested views/functions, the sorting logic
> has to take place at the very last minute - which means that it has to
> be reproduced and maintained in many different places. That's
> unsatisfactory.
>
> A similar problem having to do with encapsulation - perhaps a topic for
> another thread - is that I can't write a generic function to perform
> consistent operations against multiple tables. For example: Most of my
> applications have a number of tables that are all subject to the same
> versioning and approval logic, which is a little complex. A table-valued
> function like this would be very useful to me:
>
> Create Function GetApprovedItems(TableName as varchar(50)) returns Table
>
Why would you want to parameterize the table name in this case? I can't
be sure without seeing your table structure but one mistake commonly
made by OO programmers is to use tables like an object model - as
containers for distinct subsets of the same type of data. That gives
poor results in the relational model.
The relevant design principle is that of Orthogonal Design. Loosely
speaking we sometimes say that no two tables should share the same key
and and non-key attributes. If you follow that principle then I don't
know why you would want the same approvals process to apply to two
tables. The tables should not "overlap" so there should be no processes
that they have in common. Maybe what's missing from your model is
another table called "Approvals" - but there I'm just guessing.
Every software development discipline has its own design patterns and
practices. In the case of the relational model we are fortunate that
these are very well developed and documented. If you follow the right
design pattern then SQL should have all you need to encapsulate logic
and enable code re-use. To implement an inappropriate design and then
say that it demonstrates deficiencies of the underlying model isn't a
productive course to take whether in SQL or in an OO language or in any
other environment.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|