How to assign multiple values to an attribute of a table in MySQL? For example a table where a client can have several phones, and in assignment can be passed the several customer phones at one time.
How to assign multiple values to an attribute of a table in MySQL? For example a table where a client can have several phones, and in assignment can be passed the several customer phones at one time.
In the face of the problem of clients / phones, the best way is to create a table with telefones
since it is a relationship of many to one ( 1:N
) one customer to many telephones.
That is:
clientes
id | name | email
1 | Miguel | [email protected]
2 | Bruno | [email protected]
3 | Maria | [email protected]
telefones
id | customer_id | phone
1 | 1 | 32432342
2 | 2 | 3244234
3 | 2 | 4324234
4 | 1 | 5435435
5 | 1 | 4325423
6 | 3 | 3243324
So you can associate the client with the phone / phones it has with id_cliente
, eg Miguel has the phones 32432342, 5435435, 4325423, we see this because the id_cliente
in the phone table is 1
which is id
of Miguel in table clientes
.
EX:
The SQL
command to select the telefones
of Miguel is:
SELECT telefones.* FROM telefones JOIN clientes ON telefones.id_cliente = clientes.id WHERE clientes.id = 1
MySQL has nothing to do with it. It has a database that has an array type that allows you to do this relatively simply (nor is it that simple).
The default is to create multiple columns and leave them null when you do not have the extra data. In a database that works with variable length lines, this is not a big 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 worsening, which may or may not be a problem for your application. Here's how to do it in Miguel's response (although this is not what was asked).
A trick can be applied and use a VARCHAR
column to store this data with tabs. It is not so simple to do this and it has several complications to make queries. I only recommend it if it is very necessary, the developer is well 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.