Archives of the TeradataForum

Message Posted: Mon, 21 Nov 2005 @ 18:21:42 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Monday, November 21, 2005 13:09 -->

Michael,

I appreciate your write-up as it shows a great deal of understanding on this subject.

I do have one comment.

There should not be a choice when it comes to ensuring the model (and therefore the business) remains in a consistent state after an operation that performs a CREATE, DELETE, or UPDATE operation on an object or a TRANSFER, ASSOCIATE, or DISSOLVE operation on a relationship is performed.

Option #4 is unacceptable as it simply will not ensure the model remains consistent. Very high risk.

Options #2 and #3 are acceptable but ONLY as safety nets and safety nets only. Banking on DDL or Triggers to maintain the consistency of the model is very risky. It also makes for awkward messages between the user interface (on whatever languages implement them) and the RDBMS.

Option#1 is the ONLY choice in my opinion. The best place to enforce the business rules depicted by a data model is best served by business processes that are in close proximity to the application interface, but are easy to document, maintain, remain portable and can be manage under configuration management tools (i.e., are visible). SPs and Macros do not fit that bill very well.

In the example you provided for an EMPLOYEE and JOB ASSIGNMENT, I would have these 4 reusable (by many applications) business processes:

ASSIGN EMPLOYEE TO A JOB ASSIGNMENT (Create a new job assignment if no current assignment is found)

TERMINATE EMPLOYEE JOB ASSIGNMENT (Update current job assignment with an end date)

TRANSFER EMPLOYEE TO NEW JOB ASSIGNMENT (Update current job assignment with an end date and create a new job assignment)

GRANT LEAVE OF ABSCENSE TO EMPLOYEE (Update current job assignment with an end date)

For a software developer, those 4 business processes translate easily into individual METHODS that could be coded in a portable 4GL. Each process does one and only one thing and does it very well while enforcing specific business rules applicable to that process while minimizing regression testing if a rule were to change. At the end of each operation the business is guaranteed to remain in a consistent state (i.e., only one job assignment unless the employee is on the "pine" or on a leave of absence).

I would never endorse an application's group desire to use SP's or Macros to implement business process (and RI) because that is not in the best interest of the organization. I have seen a lot of damage done by them in the past and I see it today in my current engagement, where the Legacy systems suffer from RI problems and are highly coupled to the RDBMS to the point it practically rules out a migration of any kind unless mega-bucks are to be spent.

I would say that the use of SPs and Macros to implement business processes is an exercise in risk management and should be made ONLY after a careful review of all other options rules them as the only choice. An example of this assessment (risk vs. reward) would be the use of SPs on a Teradata DW environment for ETL tasks where the objective is to refresh the EMPLOYEE and JOB ASSIGNMENT tables from a Legacy system. On the other hand, it would not be so easy to endorse SPs if the objective is to implement an ONLINE application to capture data in a web interface or a desktop client application where a user can perform any of the four operations I listed above.

Thanks and I hope we all learned a little more about what's really important.

Anonym

	<Prev	Next>		<<First	<Prev	Next>	Last>>