Thread: Extract created and last modified data
I have a CMS with a table of items, and another table serving as a log <br /> of operations performed on the items. Therevelent table structure is <br /> as follows: <br /><p>items <br /> itm_id | usr_id_create | itm_date_create | usr_id_modify| <br /> itm_date_modify | ..... <br /><p>itm_id is a serial primary key. <br /><p>usr_id_* are the keys ofthe users who created and last modified the <br /> item. <br /><p>item_date_* are timestamps for creation and last modificationtimes. <br /><p>changelog <br /> itm_id | usr_id | log_timestamp | log_op <br /><p>itm_id and usr_id are foreignkeys into the respective item and user <br /> tables. log_timestamp is when the last change was made and log_op was<br /> the operation performed. <br /><p>I realised that the creation and last modified data was being stored <br /> intwo places, and this is causing some issues with desynching. I'm <br /> thinking of removing the creation and last modifiedfields from the <br /> items table and using a view to fetch the create data and ID, and the <br /> last modifieddate and ID from the changelog table instead. However <br /> I'm not sure of how to do this, and how it would impactperformance. <br /><p>Can anybody help with writing the query to get a view that replicates <br /> the items tableexcept with the create and last modified user IDs and <br /> timestamps loaded from the changelog table? If there isanybody who <br /> has actually implemented something similar, what kind of performance <br /> impact would it have? <br/>
I have a CMS with a table of items, and another table serving as a log
of operations performed on the items. The revelent table structure is
as follows:items
itm_id | usr_id_create | itm_date_create | usr_id_modify |
itm_date_modify | .....itm_id is a serial primary key.
usr_id_* are the keys of the users who created and last modified the
item.item_date_* are timestamps for creation and last modification times.
changelog
itm_id | usr_id | log_timestamp | log_opitm_id and usr_id are foreign keys into the respective item and user
tables. log_timestamp is when the last change was made and log_op was
the operation performed..
Can anybody help with writing the query to get a view that replicates
the items table except with the create and last modified user IDs and
timestamps loaded from the changelog table? If there is anybody who
has actually implemented something similar, what kind of performance
impact would it have?