mercredi 29 juin 2016

How to store a data whose type can be numeric, date or string in mysql

We're developing a monitoring system. In our system values are reported by agents running on different servers. This observations reported can be values like:

  • A numeric value. e.g. "CPU USAGE" = 55. Meaning 55% of the CPU is in use).
  • Certain event was fired. e.g. "Backup completed".
  • Status: e.g. SQL Server is offline.

We want to store this observations (which are not know in advance and will be added dynamically to the system without recompiling).

We are considering adding different columns to the observations table like this:

IntMeasure -> INTEGER
FloatMeasure -> FLOAT
Status -> varchar(255)

So if the value we whish to store is a number we can use IntMeasure or FloatMeasure according to the type. If the value is a status we can store the status literal string (or a status id if we decide to add a Statuses(id, name) table).

We suppose it's possible to have a more correct design but would probably become to slow and dark due to joins and dynamic table names depending on types? How would a join work if we can't specify the tables in advance in the query?

Aucun commentaire:

Enregistrer un commentaire