Design of maintenance database

database-designnormalization

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.

Best Answer

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.

Related Solutions

Beginner design of biological sampling database

I would rename TreeData to measurements.

Also, unless you really need the Visits table, I wouldn't use it. If designed properly your database would look something like this if you included the visits table:

tree db design

Using the visits table would complicate some of the queries that you're wanting answered. For example, to answer number one you might use something like this in sql server:

select
     t.treeid,
     max(m.height)-min(m.height) as Growth
from trees t
     inner join measurements m
          on t.treeid=m.treeid
     inner join visits v
          on m.visitid=t.visitid
where v.date>=dateadd(y,-2,getdate())
group by t.treeid

without the visits table you could rewrite that like this:

select
     m.treeid,
     max(m.height)-min(m.height) as Growth
from measurements m
where m.date>=dateadd(y,-2,getdate())
group by m.treeid

Sql-server – Use multiple columns for location or supertype/subtype

My approach to design is to think about the what-if's:

How would the design need to change to add a 4th type?
How would it need to change to store a common attribute?
How would it need to change to store an attribute for only one of the types?

I think after going through the process of answering the questions above for the proposed designs, it will be obvious that using a supertype/subtype structure is definitely the way to go (I dare say this is a best-practice for the situation).

There are things to be concerned about, of course -- for example, ensuring that for a given supertype, a matching record must exist in exactly 1 subtype table. With a well-written stored procedure to do "transfers" between types (which is something I assume you'll need), this should be pretty straightforward to implement.

Best Answer

Related Solutions

Beginner design of biological sampling database

Sql-server – Use multiple columns for location or supertype/subtype

Related Question