Database structure for versioning and multiple languages

Question

How can I solve the issue of content existing in multiple versions and multiple languages?

My current structure:

Each content can only have one active version in each language, and that's how I'm curious on how to best solve.

Right now I have a column of the contentversions table, which means for each change of active version I have to run a update and set active=false on all version and then a update to set active=true for the piece of content in question.

Answer 1

You could use a table activeversion with the same structure as content_contentversion_languages, but with a unique key on (content_id, language_id) and a single foreign key on all columns, referencing content_contentversion_languages. Marking a new version as active requires just a single INSERT or UPDATE to this table.