Organize the database for the consultation [closed]

Navigation
3

Very good! I will try to be specific and brief at the same time:)

I am developing an amateur app using Bootstrap to track cheese making at home. During the process the cheese goes from one room to another. In a mysql table I add with a form each batch, with the start date, kilograms, ingredients, etc.

The observations of the process are very important to me, so I want to generate a report of each lot in which I show the data of each lot, the dates in which I changed the room, and the observations collected. But it is possible that in room 1 you have an observation on July 18, and another on July 20. But I only have one column called observations.

I would like your opinions to see how to organize the data to issue and display the queries in the easiest way, because I am passionate about programming, but it is not my main activity, so I do not want to start organizing the database badly.

After consulting several answers here, I thought:

  • Option 1:

    • I create an exclusive form to generate observations, in which the room, date and comment are indicated.
    • When sending the data to the table, I create columns as comment1, date1, room1.
    • If I add more observations, three more columns would be added: comment2, date2, room1. That is, consecutively (I do not know how to do it, the truth).
    • When I generate the query I search, for each room, the comments and dates and show them.

  • Option 2:

    • Every time in the table with all the batches, insert a new batch, I create a separate table for that batch.
    • This new table would include a column with the date of each comment, and another column for the observations collected.
    • When I do the query, for that batch I extract from the second table the data in the following way: if the lot was in a room in a range of dates, I show for that lot all the observations, with their dates.

  • Option 3:

    • Use concat to add comments to each field, each of the observations by date.
    • That is, in a form I collect the date of the observation (and convert it to text), the text of the observation, and I enter both in the Comments field of that room, separating them for example with a line break. This option worries me that it is well expressed in the subsequent consultation.

Uff, in the end I got a billet, sorry. I preferred to check before starting to program like crazy.

What do you think of the options? Any other idea? Many thanks! Alex.

    
asked by Alex 19.07.2017 в 22:27
source

2 answers

3

If I understand correctly, you are starting your design, therefore you are free as to the way you want to implement it.

I would recommend that you think about the elements of your system as independent entities and then think about relationships.

What entities would there be?

  • lots
  • rooms
  • observations
  • ... other

You can have a table for each of those entities.

lotes

With the columns: 

id_lote
fecha_creacion
... etc

habitaciones

With the columns: 

id_habitacion
nombre
lugar
...etc

observaciones

With the columns: 

id_observacion
observacion

This table observaciones can be treated as an entity , for example, in the case that there are observations that are repeated very often.

In the case that it is not convenient to treat it as an entity, because it is difficult to standardize the observations that occur or for other reasons, this table should have the columns id_lote , id_habitacion , fecha and the table lotes_observaciones indicated below would not be necessary.

We already have the entities.

Now we may need some tables to establish relationships between them.

To control the entry and exit of batches in the rooms:

lotes_habitaciones 

id (autoincremental)
id_lote
id_habitacion
fecha_entrada
fecha_salida

To control the observations made on the lots:

lotes_observaciones 

id (autoincremental)
id_lote
id_observacion
id_habitacion  (si se requiere saber en qué habitación ocurrió la observación)
fecha

Then, to present the data ... it's the least of it. JOINs would be made (JOIN) and that's it.

    
answered by 19.07.2017 / 23:12
source
2

No .. none of the 3 ..

I could tell you why they are bad (very) each of the 3 .. but first we solve your problem ..

If you have the rooms, you only need a table that says the room number, the date and the comment.

The comment should not go in the main table, it should go in a secondary table, to be consulted by date or by room.

You have a relation 1: many, where for each room, you have N comments.

Long explanation of why not each of the things you say:

option 1: the system tables should not be altered, if the system is not altered. Add columns to tables does not generate more data, generates mess .. what happens when we get to comment 100, and the table does not let us add more columns ?? how do we know how many columns we have to go and search for the information ???

option 2: a new table, called tablalote1, tablalote2 .. again .. and in tablalote1543, we will not know where we are standing ... the base is armed and left armed like this, with the columns and tables necessary to contain the data.

option 3: this would not be so bad, except that to make searches you will have to go through the entire text. and your field may have a maximum of characters, unless you make it binary, and there you will not be able to search for anything except by taking everything and translating it into text.

    
answered by 19.07.2017 в 22:40
That an alert tells me that Fila eliminate Variable step by Javascript reference