Multilanguge CMS database schema, ideas, opinions

Without having read that link I’d do it by having a column that encodes the language key as well, makes it for easy and efficient lookup, making it trivial to even fall back to another with minimal network use compared to embedding and so forth.

Thanks @OvermindDL1 for the suggestion and explanations.

Also i would like to be sure that understood your point of view so I am writing the following schema
Languages migration

create table(:languages) do
      add :name, :string
      timestamps()
end

Article migration

create table(:posts) do
      add :title, :string
      add :content, :string
      add :published, :boolean, default: false, null: false
      add :language_id, references(:languages, on_delete: :nothing)
      timestamps()
end

Category migration

create table(:categories) do
      add :name, :string
      add :language_id, references(:languages, on_delete: :nothing)
      timestamps()
end

Something like this?

But the language_id to be optional only if the category or article written in another language?

I wouldn’t even have the languages table at all, I’d just use the ISO-standard language codes for the language mappings.

For posts the language_id would just be a string type for that ISO mapping. Use the elixir cldr library (I think that was it?) for handling the language codes and making sure they are correct and all. Nicely by following this style you can easily lookup codes like en-us for a US specific entry, and if it doesn’t exist you can fall back to an en generic lookup, and if that fails you can fall back to an en-% prefix lookup, and if that fails fallback to whatever you want, all in a single query is easy to do.

And for the categories table the language_id would again just be a string for the ISO language code.

I haven’t done anything super advanced with internationalization though so I could easily be missing something, but this is both what I’ve done in the past and what I’ve seen in a large variety of significantly used databases.

So something like this

create table(:categories) do
      add :name, :string
      add :language_code, :string
      timestamps()
end

and i would use a where for every language i support i guess.

Thanks @OvermindDL1 i think i finally got it.

That’s what I’d do. Plus add an index for the language code (along with probably the name after it, so an index on [:language_code, :name] perhaps would be the easiest and fastest index to make for the most usual queries I’d wager, but it all depends on what kind of queries you do).

The lib mentioned by @OvermindDL1 Cldr Languages — ex_cldr_languages v0.3.3
GitHub - elixir-cldr/cldr: Elixir implementation of CLDR/ICU · GitHub

Ah yep, that looks like it, in addition to ex_cldr itself too!

EDIT: Or perhaps just ex_cldr itself?

What are the difference because i couldn’t tell from the docs of https://hexdocs.pm/ex_cldr_languages/

Looks like ex_cldr_languages just uses ex_cldr under the hood for an easier language name conversion, but it seems you can do it all with normal ex_cldr I think?