Soo I'm working on a database that needs to support multiple languages (two for now, but who knows). I stumbled across this blog post that explains how to develop what it calls a "translation subschema" (haven't seen it called like this anywhere else so I don't know if it's actually how you'd call it), which seems like a very nice way of dealing with things.
I'm not very experienced with DB stuff, so it took me a while to fully understand what it was doing, but now that (I think) I do, I'm wondering if I could just ignore the Languages table, and just use a language field in the tables TextContent and Translations, without loosing any functionality. (except of course having a table listing the available languages, which is not however something I'm interested in)
In my head everything would still work, I'd insert stuff with
INSERT INTO TextContent (OriginalText, OriginalLanguage)
VALUES ("Ciao", "it");
DECLARE TextContentId INT = SCOPE_IDENTITY();
INSERT INTO Translations (TextContentId, Language, Translation)
VALUES (@TextContentId, "it", "Ciao");
INSERT INTO Translations (TextContentId, Language, Translation)
VALUES (@TextContentId, "en", "Hello");
and given a TextContentId, i'd retrieve the correct translation with
SELECT Translation FROM Translations WHERE TextContentId = TCId AND Language = "en"
At this point, I'm thinking I could drop TextContent too, and just have a Translations table with TextContentId, Language, and Translation, with (TextContentId, Language) as primary key.
Am I missing something? I'm trying to simplify this solution but I don't want to risk making random errors.
Edit: translations on the DB are for user inserted text, which will also provide translations. The client then will only receive text it the correct language.
Sorry, I didn't think to add in the post that the translations are in fact of user generated content, and are themselves provided by users.
Project Fluent is still a good resource tho, thank you.
And also yeah, I'll use a better schema for language tags, that's a clear fault
~~I still don't get why having a separate table for languages is useful. I mean, even if the translation changes, the language itself will remain the same, right?~~
Oh, right. Taking into account language variants makes VERY obvious why I'd want to use a table to store them.
Onestly, I just hope that won't be something i should have to worry about. The rest of the codebase is as shitty as it gets, and I don't want to be the one to refactor it for proper localization. I'm implementing a new feature that allows me some degree of movement to think about a good design for that, and new, features, but this is as far as I'll go (Yes I know I probably sound like an ass but it really is that bad)
Nah I work in shitty codebases on a regular basis, and the less I need to touch them, the happier I am.
With regards to other localization changes, it's not important to localize everything perfectly, but it's good to be aware of what you can improve and what might cause some users to be less comfortable with the interface. That way you're informed and can properly justify a sacrifice (like "it'd cost us a lot of time to support RTL interfaces but only 0.1% of users would use them") rather than be surprised that there even is one being made.
Also, user-generated content explains why these are in a DB, and now it makes a lot more sense to me. User-generated translations used as-is makes more sense than trying to force Project Fluent (or other similar tools) into it.
I mean for now it's not being requested to add other languages beside italian and english, and i'm pretty sure my employer will never care about languages he doesn't speak, so chances of languages that require some work other than translations are basically null.