Column type for support table

Asked

Viewed 129 times

0

I have a client table and would like to create a support table to avoid creating many columns (address, phone, phone1, email, Email1, Email2, car plate, mother’s name, etc.).

The support table consists of 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? (diagram below)

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

Structure:

Table: client

cliente_cod     cliente_nome    cliente_email
1               José Silva      [email protected]

Support table: cliente_fields

INT             VARCHAR(20)         ????
cliente_cod     campo               valor
1               placa_carro         ABC-9329
1               apelido             Zezinho
1               renda               5.0000
mysql sql database modeling typing
  • 1

    This question appeared to me in the analysis list because it was voted as not clear. Well, for me it is clear (and I voted to leave open). I just don’t think you should do what you’re proposing. What is the expected gain in having rows in a table for what should be columns in the client table? Will your system be customized (that is, the user can define "new columns" himself)? If the answer is no, I would make life easier and create columns in the same client table. :)

  • I agree with Luiz, this is not a good idea.

  • Did the answer solve your problem? Do you think you can accept it? If you don’t know how you do it, check out [tour]. This would help a lot to indicate that the solution was useful to you and to give an indication that there was a satisfactory solution. You can also vote on any question or answer you find useful on the entire site (when you have 15 points).

1 answer

2

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

I do not take kindly to your use in this case, but you may have some need that is not described in the question. It is losing much of the relational database advantage. If this really is necessary, it is necessary to think if Mysql is the right system for the application.

The most likely type is to use a varchar which allows you to put any information. Of course any write and read will require the application to do the proper treatment. Eventually you may have to use a blob (unlikely). Mysql now accepts a JSON type that is a specialization of varchar, may be useful in this case.

If you don’t have a data dictionary it would be interesting to record the data type as well to avoid having to rely on the programmer to get it right. You probably have some auxiliary functions to convert the data when recording and reading the data in the column. 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 carefully if it is worth adopting this strategy.

Are you sure that rent is a float? If this is ill-defined other things may be.

  • Don’t do this. Read!

  • Thanks for the answer. I thought about using float(10,2) for lace. I also thought about registering the data type and dealing with the application. I would then record 5000.00 and float(10,2) as a data type. In PHP I would handle this. The truth is that I’m in doubt if this is the best way for me. I want to avoid having a table with 30 columns. I will read now!

  • 2

    No one can say which is the best way for you. But if you don’t know what you’re going to do, the best way is to do what you do. But use float for money is the only thing I can guarantee that is a bad way in any situation.

Browser other questions tagged mysql sql database modeling typing

You are not signed in. Login or sign up in order to post.