How to create this rule in mysql

Question

I have a field on my table:

tipoEndereco char(1);

But this field only receives two possible values, which are:

  1-> i (Instalação) 
  2-> c (Cobrança)

Because of that, I’m thinking, and I’d like your opinion, to exchange it for:

tipo enum("i", "c");

The change is for performance. What do you think?

The other orientation, and the most important one, is that I would like to create a rule in Mysql that allows the insertion of empty values in the fields, if the value that arrives via query is "c" (from Billing).

That’s possible?

Here is the table:

CREATE TABLE enderecos (
  idEnderecos int(1) unsigned NOT NULL AUTO_INCREMENT,
  idClientes int(10) NOT NULL,
  tipoEndereco char(1) NOT NULL,
  endereco varchar(100) NOT NULL,
  numero varchar(5) NOT NULL DEFAULT '',
  complemento varchar(50) NOT NULL,
  bairro varchar(100) NOT NULL,
  cidade varchar(150) NOT NULL,
  estado char(2) NOT NULL,
  cep char(8) NOT NULL,
  PRIMARY KEY (idEnderecos)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

The idea here is that once the client is written into the table Clientes, get your idClientes generated (insert_id) and save address data to table endereços.

The form has two addresses, one for installation and one for billing. In the bank, the table addresses all NOT NULL. But if the address is type "c" (charging), I need the table to accept null values.

Like?

Answer 1

1st Question. Yes, there may be a mere improvement in performance, but if the amount of simultaneous transactions is small, it won’t make that much difference. The main advantage of using an Enum is that you restrict the amount of input from a user, although via code you can also do this. But if it is necessary to change or add any type or reuse the code, as in subselects, for example, you will have more problems than advantages, as described in http://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/

2nd Yes, it is possible. If you use both procedures and scripts, a CASE clause can be used for this insertion.

insert into tabela2(end_inst,end_cobr) values(select case when tipo_endereco = 'i' then Endereco else null end, case when tipo_endereco = 'c' then Endereco else null end from tabela1)

But I recommend that maybe you review the normalization process of this database, because the little I realized there may be redundant data, causing waste and loss of reliability in the database

Answer 2

I think it’s a good idea to use ENUM here, but it would have been a good idea too if you TINYINT simulating a BOOLEAN.

But let’s suppose you choose ENUM. So you need to understand the details a little bit. ENUM types are numerically indexed, which means that if you have this: ENUM type ('c', 'i'); Then internally c = 0 and i = 1. Also, if you want something like a field with default value other than NULL, you can create an ENUM field and put the value you want to be always default (if they do not provide one) at index 0, that is, the first value of the ENUM. Thus, any input other than NULL which is not a valid value will automatically be the value of the indexed at the first position of the field ENUM.
See what the documentation of Mysql says:

The value may also be the Empty string (') or NULL under Certain Circumstances:

1. If you Insert an invalid value into an ENUM (that is, a string not present in the list of permitted values), the Empty string is inserted Instead as a special error value. This string can be Distinguished from a "normal" Empty string by the Fact that this string has the Numeric value 0. More about this later.

   If Strict SQL mode is enabled, Attempts to Insert invalid ENUM values result in an error.

2. If an ENUM column is declared to Permit NULL, the NULL value is a legal value for the column, and the default value is NULL. If an ENUM column is declared NOT NULL, its default value is the first element of the list of permitted values.

So:

mysql> 
mysql> create table x (
    id int primary key auto_increment, 
    tipo enum('c', 'i') not null);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into x(tipo) values ('i');
Query OK, 1 row affected (0.04 sec)

mysql> insert into x(tipo) values (null);
ERROR 1048 (23000): Column 'tipo' cannot be null
mysql> insert into x(tipo) values ('');
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> insert into x(tipo) values ('nao existe');
Query OK, 1 row affected, 1 warning (0.09 sec)

mysql> insert into x(tipo) values ('i');
Query OK, 1 row affected (0.03 sec)

mysql> select * from x;
+----+------+
| id | tipo |
+----+------+
|  1 | i    |
|  2 |      |
|  3 |      |
|  4 | i    |
+----+------+
4 rows in set (0.00 sec)

mysql> select * from x where tipo = 0;
+----+------+
| id | tipo |
+----+------+
|  2 |      |
|  3 |      |
+----+------+
2 rows in set (0.00 sec)

mysql>