A little trouble with Revisions

[paulman888888]

Hello there;

 

I have a table that holds lyrics submitted from users but now i want to allow my users to edit other people lyrics.

 

My table is like this;

id - lyrics_title - the_lyrics

 

My question is what is the most efficant why of allowing revivsions to be made?

 

My ideas;

1) Edit all the current lyrics
- I would update each lyric by selecting it then storing in a new array then serializing and finaley put it back in.
- For every new revision i would simply unserialize add the new revision the serialize and store it again

2) Add a new coloumn
- I would add a new coloumn called parent
- For new lyrics it would have a value of 0
- If a revision is made then i would set the value of parent to the id of parent and keep track of the revisions by using order by
- This would also allow me to see who made what changes

 

If you have any ideas please let me know as this is a big change I am going to be making and I want it to be the right one.

 

Thank-you for your time

Paul

[ChrisA]

I would definitely keep revisions if possible.

 

Sounds like you would be better off having two tables - one with songID and title, then a separate one with songID, lyrics and revisionNumber?

 

If you store it all in one table, you'll end up duplicating at least the song title in many rows (see db normalization).