MySQL table design question.

[craddy]

Hello,

I'm in the middle of designing a database, some of the tables contain information (such as notices etc.) and they need to have a revision history.

I cant decide if it is better to have two tables, one for the current revision for each item and another for all of the previous revisions of the items. Or to use two fields for the index of the table - made up of an ID and a revision number.

Does anyone have any suggestions or ideas on which would be the best method - or any totally different methods achieving the same result?

If you don't understand ask and I can post more info and ER diagrams.

Cheers,

Rich

 

[craddy]

Cheers for the reply. That's what I've gone for so far:

Is there a SQL way of selecting only the greatest revision for each ID?

Thanks!

Rich

 

[craddy]

Excellent stuff! Thats exactly what I was after! Thanks so much, one quick question though - whats the name for that type of more complex query - and do you know of any good sites for learning more complex sql? I've found loads explaining the simple stuff but none with that kind of thing on.

Thanks again,

Rich