Multiple MySQL Assignment [closed]

0

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.

    
asked by anonymous 30.05.2016 / 21:17

2 answers

4

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
    
30.05.2016 / 21:31
4

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.

    
30.05.2016 / 21:27