Multilanguge CMS database schema, ideas, opinions

Phoenix Forum Chat / Discussions
,
21

I am not battling with @OvermindDL1, I respect his opinion a lot :slight_smile:

I just tell how it is done in Ruby, and coming from a multi-languages country (4) I had to solve this kind of problem.

22

Let’s say You add a published boolean field, This would have to be set in all records, or did I miss something?

23

I am sorry I think my way of expressing it was not proper what I meant is that 2 opinions you both offered have some advantages and differences.

@OvermindDL1 use the idea of languges code in each table, that speeds up the database look up.

@kokolegorille, your idea is to bring one table will act as an observer to decide which languge is needed to be displayed and your solution doesn’t repeat the langauges codes in all the tables.

I also respect both of your opinions and your experiences.

I hope that I didin’t offend any of you.

24

Oh absolutely not! You want to publish each individually. You could easily have, say, an english one published and still be working on a german one to be published later when you finish it. ^.^;

25

Ok, the published example is too simplist.

Let’s say You have a lot of metadata (position, counter cache etc.), unrelated to translation content :slight_smile:

26

Then normalize the data as per standard SQL, move that identical part out. ^.^

Shouldn’t position/counter’s etc
 be language specific too though? It’s trivial to aggregate them in sql but it’s impossible to peal them apart if needed (like to see language specific stats).

1 Like
27

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

28

Huh? What’s that?

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

Huh? o.O

29

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.

30

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

31

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

http://www.ibase.ru/files/articles/programming/dbmstrees/sqltrees.html

also

32

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

33

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.

34

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.

35

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.

36

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) :
    document.getElementsByClassName('sortable')[0];

  // https://github.com/SortableJS/Sortable
  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
          console.log(resp);
        }).catch(error => {
          // Handle error
          console.log(error);
        });
      },
    });
  } else {
    console.log("No List found.");
  }
}
1 Like
37

Thanks here is an example with vue I think it does the same thing https://codepen.io/phphe/pen/KRapQm

38

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

39

Yes, similar, but I use vanilla JS instead.

1 Like
40

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.