Ways to implement day "opening" to release data entry

Question

I am with a page that collects information from the sales team, with customer data, competition data, etc. Follow code:

CREATE TABLE dados_relatorio(
    id int(4) unsigned NOT NULL AUTO_INCREMENT,
    codigo int(10) unsigned NOT NULL,
    data_1 date NOT NULL,
    vendedor varchar(45) NOT NULL,
    supervisor varchar(20) NOT NULL,
    cidade varchar(50) NOT NULL,
    uf varchar(5) NOT NULL, 
    razao varchar(40) NOT NULL,
    ramo varchar(35) NOT NULL,
    posicionamento varchar(6) NOT NULL,
    limpeza varchar(3) NOT NULL,
    layout varchar(3) NOT NULL,
    visibilidade varchar(3) NOT NULL,
    visibilidade_i varchar(3) NOT NULL,
    materiais varchar(3) NOT NULL,
    equipamento smallint(3) unsigned NOT NULL,
    qtd_equipamento smallint(3) unsigned NOT NULL,
    tabela varchar(8) NOT NULL,
    cat_real smallint(3) unsigned NOT NULL,
    cat_premium smallint(3) unsigned NOT NULL,
    cat_paleta smallint(3) unsigned NOT NULL,
    cat_frutas smallint(3) unsigned NOT NULL,
    cat_infantil smallint(3) unsigned NOT NULL,
    cat_acai smallint(3) unsigned NOT NULL,
    cat_mono smallint(3) unsigned NOT NULL,
    cat_outro smallint(3) unsigned NOT NULL,
    dividido varchar(9) NOT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

It turns out that management wants to be linked to the mileage of the day, but these data should come in the following format: The seller "opens" the day in the system, inserts the mileage, after that, the system releases to receive the data and enter in the database. At the end of the day, "the day closes" with the final mileage. Today, after the seller’s login, he inserts the date of the visit, and collects the data. I thought I’d create another table to enter the dates and mileage, and then make some sort of parole, but I thought it was a long approach to getting to that goal.

Is there any "more" viable alternative? Because each seller will have to make this "opening" of the day with the initial mileage and close the day with the final mileage, and he can only enter the data if this opening is performed, and consequently, the new day will also require the closure of the last.... If that’s the best approach, I’ll jump in, but if there’s any other one you think better, I’d be grateful.

Thank you.

Answer 1

First I will answer the question, then I address the comment, which is a little different from what is in the question.

The modeling could be for example like this:

CREATE TABLE dados_fechamento(  
    id int(4) unsigned NOT NULL AUTO_INCREMENT,
    data date NOT NULL,
    vendedor varchar(45) NOT NULL, 
    valor_abertura INT NOT NULL,
    valor_fechamento INT
    PRIMARY KEY (id)
)

I copied the "seller" column from the "report data" table, but this is not normalized correctly, there should be a "seller" entity with attributes only of the seller (id, name for example) and use a foreign key, but not to mix with the scope of the question, Let’s leave it at once.

This table "closing data" will store the KM data, has the seller (name, therefore denormalized), the date and opening and closing values.

So, when logging in, search the seller in this table with the latest record, for example:

SELECT * 
  FROM dados_fechamento 
 WHERE vendedor = 'nome do vendedor'
 ORDER BY data DESC

With this result, what would be the logic:

• no results, so this is the first time you use this table, ask for the opening value_value and make an insert;
• has results, but the date is different from today, asks the opening value_value and makes an insert. You can also ask for the closing value of the previous date and do UPDATE:
• has results and the date is today, so already launched opening value, can follow the code or ask if you want to launch the closure. If you inform the closing, do UPDATE.

About the comment "I’d be grateful if you could explain better how FK would work in this case", needs to normalize the table data, that is, apply the normal forms of standardisation, for example extract the entities seller, supervisor and city, but this is better to have another question, because it is another scope.