Database – CMS database schema design advice

databasedatabase-designschemasql

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.

Best Answer

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

Related Topic