Column type for support table

Question

Column type for support table

Navigation

#1 by (2 votes)

0

I have a client table and would like to create a support table to avoid creating many columns (address, phone, telephone1, email, email1, email2, car nameplate, mother name, etc.).

The support table consists of the customer code, field and value.

Example:

Cliente: 1
Campo: placa_do_carro
Valor: ABC-1234

What kind of data should I use in the "value" column so I can save everything? (schematic below)

I'm confused because this table can enter income that would normally be a float and car card that would be a varchar .

Structure:

Table: customer

cliente_cod     cliente_nome    cliente_email
1               José Silva      [email protected]

Support table: client_fields

INT             VARCHAR(20)         ????
cliente_cod     campo               valor
1               placa_carro         ABC-9329
1               apelido             Zezinho
1               renda               5.0000

mysql sql tipagem modelagem

asked by anonymous 05.04.2016 / 19:58

1 answer

modeling 0..1 to 1 in the Entity Framework - Error The entity or complex Problems implementing the jQueryUI Accordion feature

score 2 · Answer 1

This default is called key value pair . This technique is useful in certain specific scenarios where it is necessary to have flexibility of which columns to fill, even columns can be created on demand according to each data entry (client in the case of the question).

I do not see your use in this case. But it may have some need that is not described in the question. You are missing out on the advantage of the relational database. If this really is necessary, one might wonder if MySQL is the right system for the application.

The most likely type is to use a varchar that allows you to put any information. Of course, all recording and reading will require the application to take proper treatment. You may eventually have to use blob (unlikely).

If you do not have a data dictionary it would be interesting to write the data type too to avoid having to be relying on the programmer to hit what you have there. It should probably have some auxiliary functions to do the data conversion at the time of writing and reading the column data. For each type you will need to think of a recording format.

It can also be difficult to make direct queries to the bank in this way. One should think hard about whether this strategy is worth pursuing.

Are you sure rent is a float ? If this is badly defined, other things may be.