Databases
StackOverflow2013
Postsdbo
PODatabase: when to split into separate tables?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODatabase: when to split into separate tables?
    primarykey
    data
    text
    <p>Say if I have two different types of sensors: one monitors analog voltage (such as on a temperature sensor) and one measures whether something is on or off (switch sensor).</p> <p>I can't decide whether to have one table:</p> <pre><code>[Sensor] Id : PK UpperLimit : FLOAT UpperLimitAlertDelay : INT LowerLimit : FLOAT LowerLimitAlertDelay : INT IsAnalog : BOOL [SensorReading] Id : PK SensorId : FK AnalogValue : FLOAT IsOn : BOOL </code></pre> <p>OR separate it all out into separate tables:</p> <pre><code>[AnalogSensor] Id : PK UpperLimit : FLOAT UpperLimitAlertDelay : INT LowerLimit : FLOAT LowerLimitAlertDelay : INT [AnalogSensorReadings] Id : PK AnalogSensorId : FK Value : FLOAT [SwitchSensor] Id : PK OnTooLongAlertDelay : INT [SwitchSensorReadings] Id : PK SwitchSensorId : FK IsOn : BOOL </code></pre> <p>At the moment I have it as one table and I use the "UpperLimitAlertDelay" as the "OnTooLongAlertDelay" when not using it as the analog sensor.</p> <p>In the code I differentiate by the boolean flag on the Sensor table and create the appropriate object (i.e. AnalogSensor or SwitchSensor) but I'm wondering if it'd be neater / more proper at the database level to separate it out.</p> <p>What rule of thumb would you use for this kind of decision? They are different entities on one level, but on another level you could say they are both just sensors.</p> <p>This is often where I can never decide what direction to take when creating a database. Maybe whenever I use a bool to differentiate what fields mean / should be used, it should really be a separate table?</p> <p>General thoughts on this topic or this sepcific problem appreciated.</p> <p>Thanks!</p> <p><strong>EDIT: Some further information.</strong></p> <p>The switch sensors monitor things like whether a door is open, a fridge compressor is running, whether an appliance is turned on, etc.</p> <p>Graphs and reports can be generated on any sensor so they are used in the same way; it's just the data will either be on/off or an analog value depending on the type.</p> <p>So basically they are generally treated the same.</p> <p>In the readings table, it is <em>always</em> one row for ONE reading off of ONE sensor.</p> <p>So far the opinions seem to be quiet subjective - I guess there are just pros and cons to both ways.</p> <p>Does the information above change anybody's opinion?</p> <p>Thanks! Mark.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMark
    1. USMark
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PODatabase: when to split into separate tables?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PODatabase: when to split into separate tables?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PODatabase: when to split into separate tables?
      1. USGennady Vanin Геннадий Ванин
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload