-1
How to assign multiple values to a table attribute in Mysql? For example a table where a customer can have multiple phones, and in the assignment can be passed the various customer phones at once.
2 answers
4
Mysql has nothing of its own that does this. It has a database that has a type array which allows this to be done in a relatively simple way (it is not so simple).
The normal is to create multiple columns and leave null when you don’t have the extra data. In databases that work with lines of variable size, this is not enough to be a major problem in most cases.
The most common solution is to do normalization and create a table to store this data that can be multiple. Of course, this has its complexity and performance worsens, which may or may not be a problem for your application. See how to do in Miguel’s answer (although this is not what was asked).
A trick can be applied and use a column VARCHAR to store this data with tabs. It’s not that simple to do this and there are several complicated queries. I only recommend if it is very necessary, the developer is very experienced to do this and understand the whole before venturing. But it is still a solution. Other types can also be used if they are more suitable, for example JSON that already has some facilities in the database to deal with this (and has been improving over time).
4
Facing the problem customers/phones the best way is to create a table with telefones since it is a relationship of many to one (1:N) a customer for many phones.
That is to say:
clientes
id | name | email|
1 | Miguel | [email protected]
2 | Bruno | [email protected]
3| Maria | [email protected]
telefones
id | id_client | phone
1 | 1 | 32432342
2 | 2 | 3244234
3| 2 | 4324234
4| 1 | 5435435
5| 1 | 4325423
6| 3 | 3243324
So you can associate the customer to the phone/phones he has at id_cliente, for example: Miguel has the phones 32432342, 5435435, 4325423, we see this because the id_cliente in the phone table is the 1 which is the id of Miguel in the table clientes.
EX:
The command SQL to select the telefones of Miguel’s is:
SELECT telefones.* FROM telefones JOIN clientes ON telefones.id_cliente = clientes.id WHERE clientes.id = 1
Browser other questions tagged mysql sql modeling
You are not signed in. Login or sign up in order to post.
If the phone number is variable you can put it in a string with a type separator
|? If it can vary between 1 and 3 you can have 3 columns and insert empty if there is... what do you think of this idea?– Sergio
I voted to close as unclear because the question asks to put several values in a table attribute. The accepted answer suggests creating another table to do this, so the question was another.
– Maniero
I put it as accepted, because I didn’t know, that it wasn’t possible to do these composite assignments at once, now took my doubt on that, and how to do it in another way, which is by creating a new table.
– Bruno Moraes Borges
I’m sorry too, it was also my mistake to have responded to something other than that, I suggested something better compared to the example customers/phones. You’re really right @bigown.
– Miguel