2

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.

  1. 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.

  2. 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?

  • How do you check equality of two tags in different languages so that you can connect them? – Tomasz Maciejewski May 4 at 0:55
  • By manually reviewing them. But that's a different question. – Homer Jay May 4 at 0:58
  • What about saving tag once in your tag table and create another table with translations? Whenever two tags mean the same you just add one more translation. Plus you pick returned tag translation based on user language. Is this solving your issues? – Tomasz Maciejewski May 4 at 1:09
  • Wouldnt this just adding a hiararchy to the Problem? 🤔 – Homer Jay May 4 at 1:34
1

Ask yourself this: What does it mean to tag an image?

It might mean one of these two things:

  1. To associate the image with some real life object, e.g. a piece of meat that comes in a tube that americans might call a sausage but a german might call a wurst or a krainer.
  2. To associate the image with a specific word, such as "sausage"

These are different activities. If you conflate them in your mind and in your code, you may end up with a confusing design.

I would suggest the true nature of this tagging activity is to associate the picture with a real life object (#1 above). This object has a string representation, of course, that depends on the language of the user-- but that is a separate thing.

So your tag list is also a list of objects:

+-------+-----------------------------------+
| TagId | Associated real-world entity      |
+-------+-----------------------------------+
| 521   |  Sausage                          |
| 522   |  Chicken wings                    |
| 470   |  Hamburger                        |
+-------+-----------------------------------+

And just allow users to tag with this short list. This design naturally prevents them from tagging an image with two words with the same meaning.

That leaves you with two problems:

  1. Data entry. How do you get the user to enter a tag if it isn't associated with a specific word in their own language?

  2. Localized display. How do you display the tag later to whatever user in their desired language?

Data entry

You didn't give specifics about how your UI works, but I assume that the user chooses a tag from a list and that list shows them what the tag means in their own language. This is usually simple to implement. You just render your list control as normal and retrieve the string for each tag from your globalized text resources.

strings.en.resx

<data name="Tag.521"><value>Sausage</value></data>
<data name="Tag.522"><value>Chicken wings</value></data>
<data name="Tag.470"><value>Hamburger</value></data>

strings.de.resx

<data name="Tag.521"><value>Wurst</value></data>
<data name="Tag.522"><value>Huhner Flugel</value></data>
<data name="Tag.470"><value>Hamburger</value></data>

You could also store these resources in a database table with a compound key of { TagID, Language code}. However it is more common to store them in flat files since they don't require the ability to be updated in real time.

Display

Displaying the image with the appropriately-translated word follows the same process. You'd detect the end user's language (e.g. from an HTTP header) and look up the string resource for each tag ID.

  • Thanks for the good counter questions. But I don't think a .resx file is suitable. The tags will be stored in an MS SQL Server. – Homer Jay May 4 at 12:46
0

From your description, I guess what you really want is this data model:

Table "Tag"
+-------+-----------------+
| TagId |Primary Language +
+-------+-----------------+
|   521 |en               +
|   522 |en               +
|   523 |de               +
+-------+-----------------+

(Primary Key: TagId)

Table "TagTranslation"
+-------+---------+---------------+
| TagId |Language |    TagName    | 
+-------+---------+---------------+
|   521 |en       | Sausage       |
|   522 |en       | Chicken Wings |
|   523 |en       | Beer          |
|   ... |         | ...           |
|   521 |de       | Wurst         |
|   522 |de       | Hühnerflügel  |
|   523 |de       | Bier          |
+-------+---------+---------------+

(TagId is just a foreign key into the Table "Tag", primary key is the combination of TagId and Language.) You can also add a "Language" table, if you like.

This model immediately solves your issue #1, since now a tag is not just "one term", but a group of terms for the same "thing" in different languages, and for tagging, the user has to pick the group, not just the term. It may also be a good idea to add a "comment" field to the tag table, for allowing to distinguish homonyms, which may exists in one language.

Issue #2 is just your decision if you want to use the "primary language" (= the language which was used by the tag creator) for a fallback strategy. If not, you probably don't need to store this language and can leave the column out.

  • In my logic, a primary language exists only from the viewers point of view. And only im Business logic. A german viewer should get the german Version, regardless in which Language it was initially tagged. – Homer Jay May 4 at 1:30
  • @HomerJay: if you don't to store a primary language, then leave that column out. It could be part of your fallback strategy in case there is no translation available in the viewers preferred language. But if you have a different fallback strategy which does not care for the language of the user who tagged the image, then there is no need for this column. – Doc Brown May 4 at 5:03
0

Both issues could be solved by relating the image to the LangGroup column. TagsInImages would become LangGroupsInImages. When connecting two translations, all affected Tags.LangGroups LangCodesInImages.LangGroup must be updated.

For a better understanding, the column LangGroup should be renamed to TagId and the former TagId becomes TagGuid

In the review process, the affected TagsInImages and Tags rows have to be updated.

+---------+-----------+----------+---------------+
| TagGuid | TagId     | 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          |
+---------+-----------+----------+---------------+

Since a review process, with some data-maintenance, has to be done anyway, this looks like an acceptable solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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