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.