MySQL table design question.

craddy · 1 Feb 2007 at 16:55

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

Dr_Evil · 1 Feb 2007 at 17:01

go for 1 table and add the Revision number in. Then just display the records with the latest revision numbers in your app or site.

craddy · 1 Feb 2007 at 17:07

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

Dr_Evil · 1 Feb 2007 at 17:16

Rule: table names in singular form - so name your table "Notice"

What fields in that table are able to be revised exactly? If all (except members of the primary key) then your design is ok.

SELECT ID, Revision
FROM Notice AS A
WHERE (Revision >=
(SELECT MAX(Revision) AS Expr1
FROM Notice AS B
WHERE (ID = A.ID)))

craddy · 1 Feb 2007 at 18:57

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