CODEWITHSUNDEEP
mysqldatabasedatabase-designrelational-database
samxli
samxli

Reputation: 1596

Good table structure for collaborative article editor

I have an app that will allow an admin to upload an article and share it with many users to edit it. The article is then broken down into sentences which will be stored as individual rows in a MySQL DB. Each user can edit article sentences one at a time. How does one structure the database to allow admins to adjust the article sentences (merge, move, delete, edit, add) and still maintain the integrity of the the user's relationship to the article sentences? Here is the basic structure:

article_sentences
---------------
-id (auto_increment)
-article_id (FK)
-paragraph_id
-content

user_article_sentences
---------------
-user_id (FK)
-article_id (FK)
-article_sentence_id (FK)
-user_content

One problem I see is the change in article_sentence ID. If the admin moves an article around, the ID will need to change along with the paragraph_id possibly changing if we want the article content to be in the correct order. To solve this, maybe we can add an article_sentence_order column? That way the id will never change but the order of the content is dictated by the article_sentence_order column.

What about merging and deleting? Those will cause some problems as well because fragmentation of the different IDs will start to happen.

Any ideas on a new schema design that will help solve these issues? How does an app like Google Docs deal with this type of issue?

Edit:

To solve the issue of moving different sentences around. We can use a new column called order_id and it can either be a varchar or int. Some tradeoffs: If int, then I will have to increment the subsequent sentences' order_id to be plus 1 of itself. If using a varchar, the order_id can simply be something like '3a' if I want to insert between 3 and 4. Problem with this is that in my application code, using numeric indexes to traverse to the next and previous sentences will be bit of a problem.

Are there other alternatives?

Upvotes: 1

Views: 369

Answers (1)

Tudor Constantin
Tudor Constantin

Reputation: 26861

What about holding only full version of content, with a version number for each record so you will have a complete history of the article edited and by whom it was modified?

User:

 - id
 - name 

User_article:

 - id
 - user_id (fk on user, this is the current editor)
 - article_id
 - version_number
 - article_content (the full content of the article)

Article:

 - id
 - created_date
 - user_id (the creator, or main owner )
 - category_id

This way, it is very easy to revert articles content to a previous point in history, to see which user what modifications made, etc

Upvotes: 1

Related Questions