Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jan 25 '12 at 14:34

This question came from our site for professional and enthusiast programmers.

2 Answers 2

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.

share|improve this answer
    
I already implemented this db months ago using the first solution. the scope of this database meant it would have insignificant impact on the hardware available, so neither space or performance was really an issue. im just asking out of interest to see how my design stacks up. –  mobiletim Jan 25 '12 at 23:55

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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