Thanks for the question, kevin.
Asked: March 29, 2017 - 8:15 pm UTC
Last updated: April 03, 2017 - 3:59 am UTC
Version: 12
Viewed 1000+ times
You Asked
I have a following requirement , we have multiple web screens with different data points but one thing common between them is that it can go through review and approval process by two different persons. I want to create a one table which accommodate dynamic data points. below has 4 datapoints , how can I improve this so that it can accommodate more or any better way of re designing this.
as I am new to oracle want to understand if there is anything better we can add in audit process to capture other than time stamp and user id.
also is it better to maintain one main table to hold the latest data like screen table and then capture through trigger in screen_audit? any recommendations on best approach to use?
create table screen_audit
( screen int,
screen_ds char(25),
screen_datapoint_val1 int,
screen_datapoint_val2 int,
screen_datapoint_val3 int,
screen_datapoint_val4 int,
screen_datapoint_txt1 char(25),
screen_datapoint_txt2 char(25),
screen_datapoint_txt3 char(25),
screen_datapoint_txt4 char(25),
audit_cd char(1),
audit_user char(30),
audit_ts char(30),
apprvl_ts char(30),
apprvl_user char(30)
)
and Connor said...
If you are expecting the number of data points to change/evolve over time, then perhaps a one-to-many relationship is better, ie
table screen_audit
( audit_id primary key,
screen int,
screen_ds char(25),
audit_cd char(1),
audit_user char(30),
audit_ts char(30),
apprvl_ts char(30),
apprvl_user char(30)
)
table screen_audit_data_points
( audit_id references screen_audit (audit_id),
datapoint_id, -- ie, 1,2,3,4, ....
screen_datapoint_val
screen_datapoint_txt
)
A couple of other things
1) Consider using varchar2 not char for your strings
2) In terms of capturing other information, check out the SYS_CONTEXT function for all the stuff that you grab about a session
Is this answer out of date? If it is, please let us know via a Comment