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:
- 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.
- 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.
- 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.