Oracle Lab DB Design (Pipeline Functions?)

Question

Main Goal: Query the database to determine what the lab technician should do next.

What I am trying to accomplish
I am designing a laboratory database where each of the following entities requires exactly one parent (to the left) and at least one child (to the right): request (REQ) -> sample (SAM) -> test (TST) -> measurement (MEA).

Each entity has the following:

• Template - (or type) of that entity (test templates might include: pH test, titration, etc)
• Custom Tables - table fields which only apply to that template
• Calculations - results of relevant formulas from data for that entity (and children)

Our specifications are often very complex. For example, a spec might state, "A third measurement may be taken if the first two are out of spec," or "Perform the long test method if the quick test method fails."

I want a function which takes an ENTITY_id and (based on already entered data and specification rules) returns a list of available child templates.

Question
Are Pipelined Functions (as described by tuinstoel below) the best way to implement the needed_children() function that I'm trying to achieve?

If not, what do you suggest?

Answer 1

You want to select master data and detail data with one select statement? Try select ..cast..mulitiset. See here: http://download.oracle.com/docs/cd/B19306%5F01/server.102/b14200/operators006.htm

EDIT1

The OP seems to want a pipelined function. You can use a cursor as in-parameter of a pipelined function. Pipelined function are very flexible. See here: http://download.oracle.com/docs/cd/B19306%5F01/appdev.102/b14261/tuning.htm#sthref2345

Answer 2

Are Pipelined Functions the best way to implement the needed_children() function that I'm trying to achieve?

I do not agree - functionally it's the same as using a temp table, but you could do the same stuff using INSERT/UPDATE statements in the parent function.

Answer 3

I don't think you really need a pipelined function. Pipelined functions are good for generators that could produce data forever. For example, you could use a pipelined function that returns fibonacci numbers. Conceptually, pipelined functions are the same like iterators in .net.

For your concerns, you could also use a non-pipelined table function. This is a function that collects (generates, calculates etc.) the data into an "array of records", and eventually returns that array. Depending on what you have to do, that can be easier to use than pipelined functions or not. If the "array of records" is something that is natural in the way you process data, it might be the better choice. Otherwise, pipelined functions might be better.

Answer 4

With such complex specifications, I would use either an object-oriented language that supports polymorphism or a rule engine like Drools (http://www.jboss.org/drools/). You can still store the data in the database and use queries to limit the amount of in-memory processing as much as possible.

With an object-oriented language (e.g. Java or maybe the procedural language of your database), you could have a Specification base class (or interface), and then implement specific behavior in subclasses. In the database you would configure where a Specification applies.

The rule engine would be even more flexible because rules are just text strings that can be changed as needed, but if have never used them there is a bit of a learning curve.