Multilanguge CMS database schema, ideas, opinions

Oh, position is to set a specific order, not to be calculated.

It’s again a Ruby example, from acts_as_list gem.

I also use lft and rgt fields quite often for nested set… and this data should not be duplicated. I use this hierarchy when dealing with Page and SubPage (and SubSubPage).

Huh? What’s that?

Never heard of so please assume no prior knowledge. ^.^;

Huh? o.O

What would You do if You want to set an arbitrary order?

I used Ruby/Rails before, that is why I often compare both.

Joe Celko’s SQL for smarties, how to represent a tree in a database.

I’ve always used the published-date for that. My usual published field (like at work) isn’t a boolean, it’s a nullable datetime field. If null then it’s invisible and that won’t change. Once published then it sets to ‘now’ so it’s visible and ordered with other datetimes in relation to that. Or I can set it in the future and it will be visible at that point. As translations are finished you can set them to all the same date if you want, but it might be useful to set a translation at a later date (like at the time it was finished) if you so wish so it gets ordered at the top of a language-specific ‘new’ page. :slight_smile:

I mostly come from web frameworks in C++, erlang, and I hate to admit it but PHP (it was paid work so eh…), never touched ruby to date. ^.^;

Heh, never heard of such a thing. Do you have a link of descriptions? I’m curious if there are ideas I can swipe. ^.^

I had a request for a product’s catalog, where user should be able to choose in which order products would be displayed.


So you use order_by? or what method did you use to accomplish this?

Sure add an order field then. Doesn’t mean other translations won’t want a different order (you’d be surprised, like big time…). ^.^

From just a quick initial look (it’s now in my to-read list) isn’t this significantly more easily solved via the path datatype in pgsql? He even says at the end you should stick to a sql server that at least has array’s in it, lol.

I use order_by position.

But it is done in a way the end user does not have to set position, it is done at the server level.

I use sortable js and the end user can freely drag and drop items to reorder them.

Oh drag and drop and update the position of the item when the user drop the item?

Can you share your method please?, I saw something similar in node.

I made some libs with webpack to build lists and trees… It uses fetch to send data to server, where the order is persisted.

const buildList = id => {
  // If no id is defined, select the first element with class sortable
  let list = id ? 
    document.getElementById(id) :

  if (list) {
    const {url} = list.dataset;

    Sortable.create(list, {
      onUpdate: (/**Event*/evt) => {
        const data = {oldIndex: evt.oldIndex, newIndex: evt.newIndex};

        // Post data as query string and use right headers
        fetch(url, {
          method: "POST",
          headers: {  
            "Content-type": "application/x-www-form-urlencoded"  
          body: toQueryString(data),
        .then(resp => {
          // Handle data
        }).catch(error => {
          // Handle error
  } else {
    console.log("No List found.");
1 Like

Thanks here is an example with vue I think it does the same thing

When it comes to SQL, I like his books a lot, but it’s not recent.

Yes, similar, but I use vanilla JS instead.

1 Like

I like Vue more because it is structured and clear, anyone that looks over the code will understand what is happening and also the vue-cli and vue-ui makes development a pleasure. You should try it sometime.

I would rather try svelte js instead :slight_smile:

1 Like

It would be good have some tutorial or book for this case with translations on ecto.
And would be better showing how to use restrictions. I’m curious about this things as well.
And when I was trying to do a Publish in Ruby on Rails I used the date like @OvermindDL1 says.
For published you have the dates for filter and for the unpublished you have the values with NULL.


I am joining @OvermindDL1 here: language codes are standard and there’s no need for IDs since it’s actually possible to have N times the same language.

I’d go with that he proposed but it’s also one of the cases I’d heavily utilise ETS caching. It’s not at all fatal if somebody hits the wrong translation in the cache 4-5 times before it gets propagated from the DB after an update.


ex_cldr handles RFC compliant language tags and also manages fallbacks to supported languages. For a somewhat extreme example:

iex> MyApp.Cldr.validate_locale "en-AU-u-nu-latn-cu-AUD-cf-account-tz-ausyd"
   canonical_locale_name: "en-Latn-AU",
   cldr_locale_name: "en",
   extensions: %{},
   gettext_locale_name: nil,
   language: "en",
   language_subtags: [],
   language_variant: nil,
   locale: %{
     currency: :AUD,
     currency_format: :accounting,
     number_system: :latn,
     timezone: "Australia/Sydney"
   private_use: [],
   rbnf_locale_name: "en",
   requested_locale_name: "en-AU",
   script: "Latn",
   territory: "AU",
   transform: %{}

Which should tell you pretty much everything you need to know. And there are other related libs for format dates, numbers, units (and soon messages) etc as required.


Thanks for the help everyone.

I decided to build a CMS called WolfCMS, I will anonunce it officially when I have an MVP.

Find more here CMS open projects ideas suggestions

1 Like

Moved everything regarding WolfCMS here WolfCMS thread suggestions and help and discussions

Thanks for the help everyone and also for the great ideas