Project rework : Should I use primary composite keys and json for translations with Laravel?

Question

Context

I am completely reworking a website (code and database) using Laravel 7 and Mysql.
The goal is to rebuild the entire project by making the best use of best practices.
In this project, I have many database tables and relations between them, and I'm also working with a lot of user-defined contents.

Problem 1

In relational database design, it's a good practice to use composite primary keys when you know for a fact that an id is useless (pivot tables for example).
But I know that Laravel does not handle composite primary keys for models and relationships, you have to install some external packages or do it yourself with Trait/Class inheritance.

So, I wonder if I should use composite primary keys, or if I should stick with a default auto-increment id along with a composite unique key?

To show an example of what I mean, I have to choose between idea 1 or idea 2, or maybe something else ?

Idea 1 : With standard primary key

Schema::create('user_documents', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained();
    $table->foreignId('document_id')->constrained();
    $table->string('file_name', 50);
    $table->json('comments')->nullable();
    // [...] Maybe some more columns
    $table->timestamps();

    $table->unique(['user_id', 'document_id']);
});

Idea 2 : With composite primary key

Schema::create('user_documents', function (Blueprint $table) {
    $table->foreignId('user_id')->constrained();
    $table->foreignId('document_id')->constrained();
    $table->string('file_name', 50);
    $table->json('comments')->nullable();
    // [...] Maybe some more columns
    $table->timestamps();

    $table->primary(['user_id', 'document_id']);
});

Problem 2

You may have noticed I used a json type for the comments column.
That's because the user could write a comment in one or many languages when he uploads a file.

Values for this column looks like this :

{
    "en": "a comment in english",
    "fr": "a comment in french",
    "*": "the comment for all other languages"
}

If he wants everyone to see the same text whatever the language, we could have this :

{
    "*": "the comment for all languages"
}

I have a lot of things working like this for user-defined multilingual content, and I thought this approach would be better than having specific tables for translations.

But I'm not sure that's a good practice to use that much json columns, is there a better thing to do for translations ?

Answer 1

You're raising a lot of good questions that needs to be answered as well. We do not have any context of your "issues" exactly precise but you gave us quite a bit of information that can be useful to understand what you're asking for.

So here are my two cents :

Problem 1

You're pointing both of the composite primary keys and composite unique keys SQL concepts which are great to handle combined keys to refer to related to a Model of your app. As firstly pointed 4 years ago (that much!) by this topic, there's indeed no way today yet to get this natively working from Eloquent. But there are workaround to make this working great.

I'd prefer to use the composite primary keys to ensure that the base is really solid and avoid making mistakes while pushing rows in the database. I'd say that it's up to you accordingly to some kind of tolerance score towards potential mistakes or gap you may have forgotten while developing your app.

I don't really like the idea n°1 because in my opinion there is no wonderful and pragmatic reason to have an auto-incremented key here. I'm always keeping the "KISS" principle in mind ("Keep it stupid simple"). SQL is offering a way to combine keys natively (and it's working very well in general purposes), "why would I go with one more key if it doesn't fit with a real useful feature of my app ?"

Laravel has grown a lot but is constantly updated and improved each month. I like this framework a lot but it's always missing valuable things and that's why open-source is so much important. It offers some way to create Traits, Interface, Helpers either to retrieve, update, and find rows by a couple of primary keys as you may have known, so don't spend too much time ; just be sure that it fit to your needs and you can manipulate your model easily through it. Create your own HasCompositePrimaryKey trait on which you can define a new primary key property : protected $primaryKey = array('key1', 'key2'); (please check this answer).

Problem 2

Indeed I also think that json is the best field type to handle multilang string values. The latest version of MySQL (8.0) has made a lot of improvements towards searching content in JSON objects that you may want to take a look at. I'd store too all the strings within the same row for multiple languages as you did for your comments table. This will allow you to easily retrieve, display and manipulate content easily according to your user or visitor's language choice. You may store the 2-letters iso lang code into the user session cookie and use it in your Blade template, where it'd be much simpler to use : $comment[$user_lang_key] where the $user_lang_key could be retrieved from some kind of Session::get('settings_lang_key').

I don't really know what is the purpose of displaying a different comment to the "all other languages" which are not available. I'd prefer to use an existing lang as the fallback for my app, like the english one which is a lot used and displayed for a lot of default values. You'd have to simply fallback the session lang key to en to make this working everywhere. Putting some kind of "alias" like the '*' can make this quite a bit confusing and will probably need you to make some pre-verification to use it properly. If you want to give the choice to your user to display the comments in lang he want to as you said in comments, I'd prefer to save this settings value somewhere in your database and use it to point to the selected language.

Also, in case your needing it (because the user of your app could setup some behaviors of your app), here's how I would handle a multi-lingual setting he could create in my e-commerce app :

{
    "fr": {
        "title": "Choix d'un T-Shirt",
        "fields": [
            {
                "label": "Taille de T-Shirt",
                "type": "single-choice",
                "choices": [
                    { "name": "S", "price": 10 },
                    { "name": "M", "price": 15 }
                ]
            },
            {
                "label": "Couleur",
                [...]
            }
        ]
    }, 
    "en": {
        "title": "T-Shirt Selection",
        "fields": [
            {
                "label": "T-Shirt Size",
                "type": "single-choice",
                "choices": [
                    { "name": "S", "price": 10 },
                    { "name": "M", "price": 15 }
                ]
            },
            {
                "label": "Color",
                [...]
            }
        ]
    }
}

Finally, everything is up to you but I wanted to share my knowledge and how I'm currently doing by my side without any pain to handle multilingual features. I hope this will give you some ideas which fit to your needs in the best way you imagine it.