Please Help With Sql Table Layout

[ricmetal]

hi guys, so, i have a couple of tables in a database, for my users. the users can add content to these tables via a web app. theres a table that holds info on photos a user has uploaded; columns = user_id, photo_name

theres another table for each user's personal details; user_id, user_name, user_email

there's another table for news entries; user_id, news_title, news_body

etc

 

i need to hold information on what users update. a user might edit a news title, add a new photo, or delete a news entry.

 

please let me know what you think a good solution to this can be.

 

i was thinking of having another table with a user_id column and then another column with keywords that translates into exactly which tables and columns have been changed, added, or deleted, but this sounds like quite alot of "translating the meaning of keywords to what actually changed in the entire database", not to mention the overhead of having to read what the column already contains to be able to add new changes.

 

any ideas on how to go about this? it the solution i came up with the worst solution? the only solution?

 

how does one go about creating an update tracking system? thanks

[fenway]

Are you trying to audit their activity? Or show this to the user? How many updates do you expect?

[ricmetal]

i need to show the updtaes to the users (not really to the users, but to the adminsitrator of the website - almost the same thing then)

regarding the number of updates, intermediate, as i dont expect the content to be updated that much.

ill be creating a list of changes made to the content of the website, so the administrator can review changes made.

[ricmetal]

anybody?

[fenway]

If the changes are minimal, you can just query the underlying tables directly, based on timestamp.

 

This is assuming the admin will be reviewing this kind of this daily -- i.e. you prepare the list to review 'often'.

 

I suppose you could log ( table, uid, user, timestamp ) too.