How to unify three records from one table in another in MySQL?

Question

How to unify three records from one table in another in MySQL?

Navigation

#1 by (1 votes)

0

I have a system that carries out a general inspection of the entire labor process on a daily basis, assigning certain values to it.

Each inspection is formed by 3 stages, where each stage has its own set of values (3 records).

The tables are as follows:

--INSPECCION--
ID_inspeccion
fecha
hora_inspeccion
etapa
realizador

--CALCULOS--
ID_calculo
valores

--INSPECCION_CALCULOS--
ID_inspeccion_calculo
inspeccion_fk
calculo_fk

The relationship is as follows:

INSPECTION will keep a unique ID for each record, next to the date, time and stage of the inspection.

There would be 3 records in the database .

INSPECCION_CALCULOS will save the id of the inspection to which the group of calculations belongs and the id of each group of calculations.

It would be 9 records, because 3 times the inspection appears, plus 3 groups of calculations .

CALCULATIONS saves the values of the calculations corresponding to each stage of the general inspection.

This is a string with values .

A serious example of a serious inspection day:

--INSPECCION--
ID_inspeccion | fecha | hora |etapa
1 | 1-3-2018 | 9am  | primera 
2 | 1-3-2018 | 2pm  | segunda
3 | 1-3-2018 | 11pm |tercera

--CALCULOS--
ID_calculo |valores
1 | 100, 200, 300
2 | 100, 200, 300
3 | 100, 200, 300
4 | 100, 200, 300
5 | 100, 200, 300
6 | 100, 200, 300
7 | 100, 200, 300
8 | 100, 200, 300
9 | 100, 200, 300

--INSPECCION_CALCULOS--
ID_inspeccion_calculo | inspeccion_fk | calculo_fk
1 | 1 | 1
2 | 1 | 2
3 | 1 | 3
4 | 2 | 4
5 | 2 | 5
6 | 2 | 6
7 | 3 | 7
8 | 3 | 8
9 | 3 | 9

I already have the insertion and the view of the data ready.

I make my inquiries using the date, because the date should not be repeated three times daily, so I get the group of 3 inspections.

My problem is : wanting to centralize these three general inspection records in a table, I do not know how to generate a unique id for each collection of three records, in order to erase the records easily.

I ask: Would it be effective to erase by date, validating (assuring) that there are only three records per day and that there can not be more with the same date?.

I thought about generating a central call table like this:

--CENTRAL--
id | id_central | inspeccion_fk

And that something like this would remain:

**CENTRAL**
id | id_central | inspeccion_fk
1 | 1 | 1 
2 | 1 | 2
3 | 1 | 3

Where the id_central is a unique value per day.

How can I unify these three records per day? Does any form of auto increase every 3 records?

mysql

asked by Victor Alvarado 01.03.2018 в 18:46

source

1 answer

FPDF Cell or Multicell FPDF cell does not fill with color and gets a line break that I do not want

score 1 · Answer 1

You do not need three tables, you only need two. What you are using is a "one to many" relationship, which is an extremely common and highly studied type of relationship. Essentially, each CALCULO always belongs to one and only one INSPECCION , so you can store the latter's ID in a dedicated field.

**INSPECCION**
id
fecha
etapa
realizador

**CALCULO**
id
inspeccion_id
valores

So, you can load everything with a JOIN: SELECT * FROM INSPECCION INNER JOIN CALCULO ON INSPECTION.id = CALCULATION.inspection_id WHERE id =?

Where "?" is the id of the inspection to load.

To store it, you would first need to do an INSERT on INSPECTION and then, using the ID of that transaction, INSERT CALCULATIONS.

However, I strongly recommend using an ORM to make your life easier (the choice of ORM will depend on the language you are using and eventually on the framework if there is one).