MySQL - Coming up with a Unique Key for each record, not the primary Key

Question

Ok this is a tricky one to explain.

I am creating an app that will have PAGES, currently I'm using PageID as the key to SEL the record.

The issue I'm having now is that I want users to be able to EDIT pages, but not lose the previous page (for history, recording keeping reasons, like a changelog or wiki page history).

This is making me think I need a new field in the PAGE table that acts as the pageID, but isn't the Primary Key that is auto-incremented every time a row is added.

Google Docs has a DOCID: /Doc?docid=0Af_mFtumB56WZGM4d3Y3d2JfMTNjcDlkemRjeg

That way I can have multiple records with the same Doc ID, and show a history change log based on the dataAdded field. And when a user wants to view that DOCID, I simply pull the most recent one.

Thoughts? I appreciate your smart thinking to point me in the right direction!

Answer 1

each document is really a revision:

doc - (doc_id)

revision - (rev_id, doc_id, version_num, name, description, content, author_id, active tinyint default 1)

then you can open any content with just the rev_id: /view?id=21981

select * from revision r, doc d where r.rev_id = ? and r.doc_id = d.doc_id

Answer 2

Keep an audit table with the history of the changes. This will allow you to go back if you need to roll back the changes or view change history for example.

Answer 3

You might model it like this:

• An app has multiple pages, a page has multiple versions (each with some version info (e.g., date, edit count), and a foreign key to its page)
• Viewing a page shows the most recent version
• Saving an edit creates a new version

Answer 4

You're on the right track. What you need is a history or revision id, and a document id. The history id would be the primary key, but you would also have a key on the document id for query purposes.

With history tracking, you add a bit more complexity to your application. You have to be careful that the main view of the document is showing the current history revision (ie. largest history id for a given document id).

As well, if you are storing large documents, every edit is essentially going to add another copy of the document to your database, and the table will quickly grow very large. You might want to consider implementing some kind of "diff" storage, where you store only the changes to the document and not the full thing, or keeping history edits in a separate table for history-searching only.

Answer 5

UUID() creates a randomly generated 128bit number, like '6ccd780c-baba-1026-9564-0040f4311e29' This number will not be repeated in a few millions years.

//note most digits are based upon timestamp and machine information, so many of the digits will be similar upon repeated calls, but it will always be unique.

Answer 6

This sounds like a good job for two tables to me. You might have one page_header table and one page_content table. The header table would hold static info like title, categorization (whatever) and the content table would hold the actual editable content. Each time the user updates the page, insert a new page_content record versus updating an existing one. When you display the page just make sure you grab the latest page_content record. This is a simple way to keep a history and roll back if needed.

Good luck!