Is using the Mariadb JSON type a good one for saving primary keys?

Question

I’m developing a database for a local travel agency:

I need to pick up all the passengers and store them in one entity Viagem, so that in the "passengers" field of the latter would be of the JSON type, in which would be a array of foreign passenger keys.

Example:

-- Retorna um array de ids de passageiros da última viagem criada.
SELECT passageiros FROM viagens 
ORDER BY created_at DESC
LIMIT 1;

-- retorno
+---------------+
| passageiros   |
+---------------+
| [1,2,3,4,5,6] |
+---------------+

The question is: do you think it’s a good store like this? After all, when you need to add the id on the trip, just use the function JSON_MERGE_PRESERVE() of Mariadb, which would preserve the others ids, and add the next one at the bottom of the list. And when I needed to research the passengers of the trip, all I had to do was make an algorithm that looked up the ids contained in this array.

Is a good?

Answer 1

Not at all. Primary keys must be stable, If mute is not a primary key, this data is clearly secondary, I do not know if it is appropriate to access so, but as primary key it certainly does not serve. Then use a id or see if you can use any other stable key.

You can’t be sure, but it looks like you can’t even be guaranteed to be unique, that would be an even bigger problem.

It’s not that the database doesn’t accept it, but conceptually it’s wrong to use something like this. I could even use JSON, but using it right almost always won’t make sense.

Nor did I get into the merit that this will potentially be a very big key.

Note that I did not say that your entire table is wrong and should use another model (I have no way of knowing if you need it without more details), I spoke only of the primary key, power that the table is ok, it may be that this JSON column can be a good to meet your demand, as long as it’s not the primary key. The most correct of the relational model is to create another table, but this should not always be the solution anymore, the theory was created in the 70’s when no one thought or had computational resources that we have today.

There are some questions that might help understand this:

• It is necessary to create a primary key with auto increment?
• Values that can be entered as primary key
• Surrogate Key and Natural Key
• Generate primary key in the application or keep it as auto incrementable?
• Alternative to CPF (Foreign Users)
• Difference and use between Primary Key and Foreign Key
• A primary key can be used on a foreign key?
• It is recommended to use natural primary key?

Answer 2

The correct is to have a table that makes the relationship between passengers and travel, functional example:

CREATE TABLE IF NOT EXISTS passageiro(
    _id INT AUTO_INCREMENT,
    nome VARCHAR(255) NOT NULL,
    PRIMARY KEY (_id)
);

CREATE TABLE IF NOT EXISTS viagem(
    _id INT AUTO_INCREMENT,
    nome VARCHAR(255) NOT NULL,
    data_ini DATE,
    PRIMARY KEY (_id)
);

CREATE TABLE IF NOT EXISTS viagem_passageiros(
    _id INT AUTO_INCREMENT,
    viagem_id INT,
    passageiro_id INT,
    PRIMARY KEY (_id),
    FOREIGN KEY (viagem_id) REFERENCES viagem (_id),
    FOREIGN KEY (passageiro_id) REFERENCES passageiro (_id)
);

insert into passageiro(nome) values ('SemNome1');
insert into passageiro(nome) values ('SemNome2');
insert into passageiro(nome) values ('SemNome3');

insert into viagem(nome,data_ini) values ('Roma', now());

insert into viagem_passageiros(viagem_id, passageiro_id) values (1,1), (1,2), (1,3);

To return as a list you can use:

select B.nome, GROUP_CONCAT(C.nome) as passageiros from viagem_passageiros as A
Inner join viagem as B on A.viagem_id = B._id
Inner join passageiro as C on A.passageiro_id = C._id

Sqlfinder: http://sqlfiddle.com/#! 9/d3aeb8/1

You can do it with Json?

yes you can create the Json type field, but you may have many future problems, such as migrating to another database that has no support, data manipulation may become more complex, user that does not exist can be added to the list since there is no validation in the database unless you do a function that does the validation... nothing will be simple using this solution.