DB design : Config Data, Actual Data, Log Data

I want to know if there is any typical approach to differenciate this kind of data

I have to 开发者_开发问答listing devices (for example) in a db, ane everyone will have

• Configuration data
• Actual data
• Log data

I commonly mix Config/Actual Data in the same table and another table for Log data,

This seems to be an usual issue, so I wonder if there is any standar way to do it.

EDIT::

Here an Example

A semaphore at the street :

• Config data = position (street intersection), type (for passengers, cars), etc..
• Actual data = colorstate=red, green, walk, stop.. functionality=ok, being repaired, etc..
• Log data = date/time + colorstate + (any other of the Actual data needed to be logged)

Thanks

---

You might divide it up like so (being lazy about sql syntax and types):

`

signal_config

id (key) position type

signal_log

signal_id, timestamp (compound key) color_stat one of (red, yellow, green) functioning_state `

To my mind, there's stuff that doesn't change about the signal, like it's location and type, and stuff that does change like its repair state and color. With this table you could query for the times on a particular day that the light was both working and red.

---

I think there's a bit of confusion over terminology here.

What you're calling "config data" sounds to me like "business key" or "candidate key": a set of data which is (probably) unchanging and which uniquely identifies the entity: there is only one traffic light at the end of Main Street.

What you're calling "actual data" seems like the attributes of an entity, which change over time.

So I don't think there is a design problem putting them in the same table.

Also it is a very good idea to record "log data" (audit trail, history) in a separate table.

---

it looks like you would benefit from properly normalizing this information into several tables.

it does not look like you should consider jamming it all into one table.

---

Your config and actual should go into different tables. The config seems to be rather static and when written to a table experiencing very few writes will not fragment and degrade performance over time. The actual data table would be smaller and could then be optimized for the relation back to the config using indexes, partitioning, pad index, etc. The actual table may fragment more often but should be able to be rebuilt quickly because it contains less data.

Optimize the log table in a way which benefits your prevelant search method. If you search by date range on most occasions cluster the table by logged date. If the search is by an id of a config item consider partitioning or just cluster by the id and permit the table to fragment, and leave it fragmented based on the id of the config item.