stack-overflow_virtual-column-discussion

-- Stack Overflow Test Plan for:
-- http://stackoverflow.com/questions/24276153/oracle-pl-sql-with-automatic-values
-- (prepared by Richard Pascual)

DROP TABLE "PROCESSED_PRODUCT_WEIGHT";

CREATE TABLE "PROCESSED_PRODUCT_WEIGHT" (
    "PRODUCT_NAME"  VARCHAR2(40) NOT NULL,
    "PROCESS1"      NUMBER,
    "PROCESS2"      NUMBER,
    "WEIGHT"        NUMBER,
    "TOTAL_WEIGHT"  NUMBER GENERATED ALWAYS AS ((PROCESS1 + PROCESS2)*WEIGHT) VIRTUAL,
    "RECORDED_DATE" DATE,
    CONSTRAINT  "PROCESSED_PRODUCT_WEIGHT_PK" PRIMARY KEY ("PRODUCT_NAME", "RECORDED_DATE")
)
/

-- Test Case "Initialize"
INSERT INTO PROCESSED_PRODUCT_WEIGHT ("PRODUCT_NAME", "PROCESS1", "PROCESS2", "WEIGHT", "RECORDED_DATE")
       VALUES ('ITEM1', 0, 0, 10, '01/01/2014')
INSERT INTO PROCESSED_PRODUCT_WEIGHT ("PRODUCT_NAME", "PROCESS1", "PROCESS2", "WEIGHT", "RECORDED_DATE")
       VALUES ('ITEM2', 1, 1, 10, '01/01/2014')
INSERT INTO PROCESSED_PRODUCT_WEIGHT ("PRODUCT_NAME", "PROCESS1", "PROCESS2", "WEIGHT", "RECORDED_DATE")
       VALUES ('ITEM3', 1, 1, 15, '01/01/2014')

-- Test Case ONE
INSERT INTO PROCESSED_PRODUCT_WEIGHT ("PRODUCT_NAME", "PROCESS1", "PROCESS2", "WEIGHT", "RECORDED_DATE")
       VALUES ('ITEM1', 2, 5, 10, '02/14/2014')
INSERT INTO PROCESSED_PRODUCT_WEIGHT ("PRODUCT_NAME", "PROCESS1", "PROCESS2", "WEIGHT", "RECORDED_DATE")
       VALUES ('ITEM2', 3, 1, 10, '02/14/2014')

-- Test Case TWO
INSERT INTO PROCESSED_PRODUCT_WEIGHT ("PRODUCT_NAME", "PROCESS1", "PROCESS2", "WEIGHT", "RECORDED_DATE")
       VALUES ('ITEM1', 2, 5, 55, '03/14/2014')

-- Test Case THREE
INSERT INTO PROCESSED_PRODUCT_WEIGHT ("PRODUCT_NAME", "PROCESS1", "PROCESS2", "WEIGHT", "RECORDED_DATE")
       VALUES ('ITEM3', 2, 1, 20, '04/01/2014')

/



-- Below is the initial response to the Stack Overflow question referenced:

#Declaring VIRTUAL (Computed) COLUMNS IN Oracle TABLE Design

I'll agree with most of what has been said so far with some additional elaboration.  My starting table design looks similar but it too is also inaccurate for some use cases as explained below.

<!-- language:SQL -->

        CREATE TABLE "PROCESSED_PRODUCT_WEIGHT" (
           "PRODUCT_NAME"  VARCHAR2(40) NOT NULL,
           "PROCESS1"      NUMBER,
           "PROCESS2"      NUMBER,
           "WEIGHT"        NUMBER,
           "TOTAL_WEIGHT"  NUMBER GENERATED ALWAYS AS
                           ((PROCESS1 + PROCESS2)*WEIGHT) VIRTUAL,
           "RECORDED_DATE" DATE,
           CONSTRAINT  "PROCESSED_PRODUCT_WEIGHT_PK"
              PRIMARY KEY ("PRODUCT_NAME", "RECORDED_DATE")
           )
           /


##Previous Suggestions and Assumptions

***Table Bound Attribute Properties***:  The table construct used by [@Bob Jarvis](http://stackoverflow.com/users/213136/bob-jarvis) is also known as a `VIRTUAL COLUMN`.  It works well because the definition of `TOTAL_WEIGHT` is entirely dependent on other values contained within the same table.

***SQL Query Associated Calculation:*** On the other hand, [@Nishanthi Grashia](http://stackoverflow.com/users/3492139/nishanthi-grashia) and [@OldProgrammer](http://stackoverflow.com/users/1745544/oldprogrammer) both recommend modifying the value within each SQL query executed against the database.

*BOTH* Cases may work assuming that the mass per unit of the product does not change during the lifetime of the production cycle.

> An example where this assumption is not flexible is if the products consist of units of varying volume.
>
  + Since it was not mentioned in the OP, consider this possibility:
  + Products ITEM1, ITEM2 and ITEM3 have variable weights per unit.
  + They are all produced in a coffee packaging plant.
  + Each item can be a type of coffee bean and its source.
  + "Processes" could be bean "treatments" such as decaffeination, roasting type or flavor infusion.

The "units" could be packaging of varying sizes.  This would mean that package volumes would have a direct effect on the mass (called "weight") per product unit counted.


##Test Cases for Identifying the Effect of Changing Unit Sizes

Each test case shows how a virtual column does not satisfy the possibility of variations in the unit sizes and masses of each product over time.

![Initial Table Population][1]

**Test Case One:**

![Test Case One:  Change Process Quantites for Two Different Item Types][2]

*For production observations made 2/14/2015*

**Test Case Two:**

![Test Case Two:  No Process Quantities Counted/Incremented][3]

*The mass per unit processed on 3/14/2014 is increased only, skewing the total mass produced since the item quantities made previously are multiplied by a larger value through the virtual column definition.*

**Test Case Three:**

![Test Case Three:  Process Quantity and Mass per Unit Changes][4]


##Data Output and Results

![Result Snapshots for each production period][5]

*Above are the test results associated with all three test cases.*  the resulting values are not correct for the use cases created.  They demonstrate that for a changing weight value, the virtual/calculated column formula and approach gives incorrect results.


##A Discussion of Alternate Solutions

1. The trigger approach may work for maintaining calculated values for `TOTAL_WEIGHT`.  Incremental changes (updates) are appended to the current, existing value as each component varies.

2. Force all DML through a single DML operation contained in a CRUD package.  The problem with defining an embedded SQL statement to enforce requirements is that other processes and their developers will need to be familiar with what your isolated PHP form/page does within your app in order to duplicate it for their own operation.

3. If there is a concern about overhead or possible locking of the main table, then consider introducing a composite key: `PRODUCT_NAME` + `WEIGHT` where the system that UPDATES data is already aware of the unit weight of the new count of items it is adding.


  [1]: http://i.stack.imgur.com/3HeDr.png
  [2]: http://i.stack.imgur.com/g2dOP.png
  [3]: http://i.stack.imgur.com/4AIF2.png
  [4]: http://i.stack.imgur.com/qh8Cv.png
  [5]: http://i.stack.imgur.com/yJz8x.png