Virtual columns made their debut in Oracle Database 11g. They are a value that’s not stored with the row, rather they are computed as queried. [DOCS]
So I could have a column of type DATE.
And I could have a virtual column of type VARCHAR2 that is computed by running a function against the previously mentioned DATE column.
How would I model this?
In your relational, or even logical design, you can set a column’s property as COMPUTED. Don’t look for a ‘Virtual’ property, you won’t find it.
Don’t provide the AS keyword, just the ‘code’ – which you’ll see once you preview the DDL.
What About in the Logical Design?
In a proper logical model, the business owners should probably know enough to know that certain values are based on the values of other attributes. So, no need to start worrying about this in the Relational model – start documenting it up front in the logical step.
4 Comments
Great tip. Glad to see there is a good way to define virtual columns as they can be very useful. However, in a proper (theoretical) 3rd Normal Form (3NF) Logical model you would not include them as derivations are actually a form of denormalization since they are calculated from (and therefore dependent on) other attributes in the relation.
Just sayin… 🙂
How many proper 3rd Normal Form logical models do you run into?
I agree.
So, how would you document this in your design, if not as an attribute? As a note?
1st as a note on the attribute in the logical model (assuming we want a pure form), then as you showed as a virtual column in the relational (physical) design.
In my standards check list when there was a derived column added, I required a note/comment with the formula as well as a reason for the denormalization (even at the table level) to make sure people thought about why they were doing it. Normally the reason was performance and consistency if it was a derivation required in reporting on a regular basis. With a virtual column, performance is not likely the justification but consistency still is. In the past, we often did calculations inside a report tool or at best in a view between the table and the reporting layer. That could be risky as different report writers might define the calculation differently.
So I do think it is a great option now to just add a virtual column to the table directly and expose it to the reporting tools that way. In fact it simplifies the architecture (by eliminating the need for a view layer) and enables a more agile approach to development as the virtual columns can be added, dropped, or changed pretty much on the fly based on user feedback.
Thank you! This was a great tip and one I didn’t know about.