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/>
