I am developing a web application where users can upload images and add tags to them. Because the audience is very international, I want the app as well as the content to be localizable. Most of the tags have a counterpart in all/other languages but some won't.
Current situation
Images table
+---------+--------------+
| ImageId | ImageData... |
+---------+--------------+
| 32 | img1.png |
| 33 | img2.png |
+---------+--------------+
TagsInImages table
+-------+---------+
| TagId | ImageId |
+-------+---------+
| 521 | 32 |
| 522 | 32 |
| 342 | 33 |
| 123 | 33 |
+-------+---------+
Tags table
+-------+---------------+
| TagId | TagName |
+-------+---------------+
| 521 | Sausage |
| 522 | Chicken Wings |
| ... | ... |
| 342 | Hamburger |
| ... | ... |
| 123 | Beer |
+-------+---------------+
Target
- If a user tags an image, the tag is seen as the user's local version.
- The tag should be connectable to other language versions.
- When the tag is new and created, there is no connection and/or translation. This happens maybe later or maybe not.
- On the final tagged image, there should be shown the tags of the viewer's language. (Different person/language) with the option to show tags in other languages. This could also be a fallback if not tags in the viewer's language exist.
My idea is the following:
+-------+-----------+----------+---------------+
| TagId | LangGroup | Language | TagName |
+-------+-----------+----------+---------------+
| 521 | 521 | en | Sausage |
| 643 | 521 | de | Wurst |
| 324 | 521 | ru | Kолбаса |
| 986 | 521 | es | Salchicha |
| ... | ... | ... | ... |
| 522 | 522 | en | Chicken Wings |
| ... | ... | ... | ... |
| 470 | 420 | en | Hamburger |
| 465 | 420 | de | Hamburger |
| 420 | 420 | es | Hamburguesa |
| 455 | 420 | jp | ハンバーガー |
| ... | ... | ... | ... |
| 111 | 123 | en | Beer |
| 123 | 123 | de | Bier |
+-------+-----------+----------+---------------+
Workflow:
The user is tagging his new uploaded images. To achieve that he uses a control like the one at StackExchange when asking a question. It must be as simple as possible. Users are not expected to be able to categorize or even do data maintenance. While typing the control makes suggestions based on the existing tags in the upload uploaders language. But these are just suggestions. If the tag does not exist it will be created.
An American user uploads the first image ever of cheese and creates a new tag "Cheese"
+-------+-----------+----------+---------+
| TagId | LangGroup | Language | TagName |
+-------+-----------+----------+---------+
| ... | ... | ... | ... |
| 987 | 987 | en | Cheese |
| ... | ... | ... | ... |
+-------+-----------+----------+---------+
Later a Spanish user uploads an image of cheese and creates the new tag "Queso"
+-------+-----------+----------+---------+
| TagId | LangGroup | Language | TagName |
+-------+-----------+----------+---------+
| ... | ... | ... | ... |
| 864 | 864 | es | Queso |
| ... | ... | ... | ... |
+-------+-----------+----------+---------+
The newly created tags get reviewed by the mods who are taking care of the quality. (Correcting typos, ....) An English and Spanish speaking mod recognize that "Cheese" and "Queso" are translations of the same word and connects them by updating the LangGroup.
+-------+-----------+----------+---------+
| TagId | LangGroup | Language | TagName |
+-------+-----------+----------+---------+
| ... | ... | ... | ... |
| 864 | 864 | es | Queso |
| 987 | 864 | en | Cheese |
| ... | ... | ... | ... |
+-------+-----------+----------+---------+
But I have two issues with this.
Different language versions of a tag can be used like different tags. You can assign "Sausage" (521) and "Wurst" (643) to the same image. It must be prevented in business logic.
The image creators language version is kind of primary tag. The other languages are only indirectly assigned. But from the business logic point of view, they have the same weighting. It must also be solved in the business layer.
So coming to the point, what can I do to solve my issues?