1

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
  • 2
    Belongs on dba.stackexchange.com May 21, 2014 at 16:07
  • 2
    No, this is fine here, its a conceptual development question. May 21, 2014 at 16:26
  • 4
    The second approach is a perfect example of a violation of the first normal form: The domain of each attribute contains only atomic values, and the value of each attribute contains only a single value from that domain.
    – abl
    May 21, 2014 at 18:35

3 Answers 3

6

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
  • Yes, the [time] is related to the [process]. While all the responses are really good, I'll go with your response because the query performance will suffer with the second approach as you explained in your response. Thanks.
    – Jasper
    May 22, 2014 at 8:59
2

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.

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.