I am designing a CMS, and would like to start with a simple database schema, with the requirements

  1. Two main types of entity,Post and Category
  2. For Post, it need to support multilingual and versioning
  3. For Category, only multilingual support is needed.
  4. For both type, hierarchical relationship support (see the parent field) is needed.

Now, I have prepared a simple design like the following

enter image description here

I want to seek your expert advices if any improvements can be done or corrections are needed.

1 Answer 1


A CMS model looks like something complex but lets try to think up something.

  • It seems that the relationship between POST and POST_VERSION should be 1:M
  • LANGUAGE entity is missing
  • POST_VERSION_LANGUAGE should be a join entity between LANGUAGE and POST_VERSION
  • COLLABORATOR can have specific ROLES regarding a POST_VERSION, like: authos, translator, proofreader, illustrator, photographer, etc.

Since a PNG is worth 1024 words, here's a conceptual model. I'll be adding edits as you ask questions in the comments section:

enter image description here

  • Thanks for your answer, very nice indeed. JUst two questions: What is ROLE and why there is a linkage between COLLABORATOR and LANGUAGE?
    – Yoga
    Aug 13, 2016 at 3:52
  • @Yoga Role is whether a given collaborator is the author of the article, or the translator, or the proofreader, etc. The rows of that table would be: author, translator, proofreader, illustrator, photographer, etc. The relationship between collaborator and language is because the CSM will have multilingual content (as your original design seems to imply by having post_version_language) and each collaborator can have one or more languages in which he is proficient. That would help choose translators or authors for different languae versions of the articles. Aug 13, 2016 at 12:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.