How to organize a database for a multilingual online file cabinet?

Question

Database design

I ran into a problem when creating a multilingual card index for manuals.

The task is to link separate paragraphs of the text of one language with the corresponding paragraph of another language, while taking into account the author, the manual itself from which the paragraph was taken, etc.

The paragraph is chosen as the minimum unit from which to build because each paragraph will be assigned tags.

So far, the outline of the structure looks like this:

Table authors :



the uuid field will be the same for different translations of the same record in the database.

For example:

id | uid | first_name |last_name| content | language

1 | 9at29342-2aa7-41b2-ae37-4mk7470b7244 | John | doe | Few words about John Doe | us
2 | 9at29342-2aa7-41b2-ae37-4mk7470b7244 | John | Dow | A couple of words about John | en
3 | 9at29342-2aa7-41b2-ae37-4mk7470b7244 | John | doe | Ein paar Worte über John Doe | de

Table manuals :



Table paragraphs :



Table languages ​​(binding with the rest of the fields occurs by languages.code == anytable.lang ):



The problem faced is this duplication of information for example.
There is lang for manual for author and for paragraph. Although, in theory, it would be correct to indicate lang in only one table.

In general, I will be glad to any comment, help in structuring the architecture or comments.

Thanks in advance.

Answer 1

Take out the localization of paragraphs in a separate table.
Translation table paragraphs_locale:
id
uuid
manualId = manuals.Id
authorId = Authors.id
changeAuthorId = Authors.id
parentParagraphId = paragraphs_locale.Id Can an author know several languages ​​and write translations?
lang
content
revision is just a counter incrementing upward with each change.
created
updated
I moved the paragraphs to a separate paragraphs_locale table. There can be many translations of a paragraph and they can be done by different people, including the author himself. Other people always base on an existing paragraph, so there is parentParagraphId and lang where lang is the target language they want to add. Here the concept of the author is divided. The author can be one person, but others can make changes, so there is a changeAuthorId to indicate the last person to make changes.