design of maintenance database

Question

Lets say i am designing a database that tracks maintenance of equipment.

Each piece of equipment has various fields that usually don't change eg. ID, model, serial number etc.

Some fields change frequently - during maintenance eg, status, location, firmware, configuration options etc. And these fields should keep a history.

Is it better to have one table with all these frequently change fields? eg tracking table, each change to any one field will create a new record, copying the field values from the previous (with updates) and the current date time.

OR better to keep separate table for each field? eg status table, location table, each table will have own date time, edited by user etc fields

One benefit i see in the first method is easier to look up the date and find the state of all fields at that time. The drawback perhaps is use up more space and some field may change more frequently than others.

Answer 1

IMO It is much better to have a design with multiple tables. You will be able to track changes more effectively that way.

Also, for the fields that change, I would not go with a Name-Value pair of storage. I would actually create tables with appropriately named columns.

Is data space really an issue for you? You should run a few calculations estimates to see if space is really going to be an issue. You'll be very surprised to see how much of a non-factor hard disk space is.

Answer 2

You might consider something in between one maintenance tracking table and a maintenance table for every field.

The fields that change value every time maintenance is done should be put in one maintenance tracking table.

The fields that change value occasionally (not tied to a particular maintenance event) could be put in one other table. The table would have a value type column, the value, and the time stamp of the change of value.