We have a database that stores the process/history of particular machines. What I mean by that is that we have “started”, “updating”, “shutdown”, and so on for all the computers in the company.
The requirement is to have the history available in the right order. Simple enough. My approach was to have a simple database table like this:
counter | machine | time | process | ... 1 | PC1 | ... | started 2 | PC2 | ... | updating 1 | PC1 | ... | updating
The query will be very simple because I can simply sort by the counter.
My colleagues suggested a very different approach. My gut feeling says it is wrong but I don’t have enough arguments against it. Can you help define what is wrong with it?
Instead of using multiple rows for the process of a machine, they want to write them sequentlly into the same field. Same goes for the time field. Their main arguement is that we save storage
counter | machine | time | process | ... 1 | PC1 | ... ... ... | started updating shutdown 2 | PC2 | ... ... ... | started updating
3
The second approach produces ambiguous results: what is the value in the time column? the time of started? or shutdown? or something else?
The second approach also makes it harder to query for machines that have a started and updating process and no shutdown process, which could be useful for creating reports and live monitoring. Sure you could use like... but that tends to be slower than when you can match a value exactly, and even slower if you index the column, or just store an ID that references a process name.
The second approach might save some storage, but how much data do you plan on having? Millions of new records every few days? More? Unless storage is a serious problem even at this design stage, I would not use the second approach.
1
That’s a fine idea, assuming that your database vendor has no maximum text field size. I could also see there being concurrency issues on the rows when two threads are trying to append to the same row near simultaneously.
Ask your team why space is that much of an issue that you would try to make bad optimisations that will save very little space compared to the problems they might cause.
The first method is better. The second method will need a column with no limit of length. Unless both of you have known the maximum characters that will be put in.
The second method will not allow you to monitor the process, step by step like the first one.