Linear database design

user_7718409 0
Viewed 211

I have a question about database relationship

I am trying to build a monitoring system with the following rules :

  • Channels belongs to one Sensor
  • Sensors belongs to one Device
  • Devices belongs to one Probe
  • Probes belongs to one Core

Here is a preview of the tables

+-------------+    +-------------+
| Cores       |    | Probes      |
+-------------+    +-------------+
| id          |    | id          |
| fields ...  |    | fields ...  |
+-------------+    | core_id     |
                   +-------------+

+-------------+    +-------------+    +-------------+
| Devices     |    | Sensors     |    | Channels    |
+-------------+    +-------------+    +-------------+
| id          |    | id          |    | id          |
| fields ...  |    | fields ...  |    | fields ...  |
| probe_id    |    | device_id   |    | sensor_id   |
+-------------+    +-------------+    +-------------+

Now to get the core_id of a spécific channel or the full list of a core's channels, I need to join all the five tables.

My question is, would it be better to have all tables linked together like the following example or it's a bad database design.

  • Cores(id, fields...)
  • Probes(id, fields..., core_id)
  • Devices(id, fields..., core_id, probe_id)
  • Sensors(id, fields..., core_id, probe_id, device_id)
  • Channels(id, fields..., core_id, probe_id, device_id, sensor_id)
relationship relational-database database-design foreign-keys database
2 Answers

One thing to consider is: "can X exist out of the context of Y?". Design changes depending on the answer. Based on your question, and design, these are the answers:

|            Question                 |Answer|
+-------------------------------------+------+
|Can a core exist independently?      | Yes  |
|Can a probe exist without a core?    | No   |
|Can a device exist without a probe?  | No   |
|Can a sensor exist without a device? | No   |
|Can a channel exist without a sensor?| No   |

As per these answers, a viable logical design may be:

-- Core COR exists.
--
core {COR}
  PK {COR}

-- Probe number PRO_NO of core COR exists.
--
probe {COR, PRO_NO}
   PK {COR, PRO_NO}

FK {COR} REFERENCES core {COR}

-- Device number DEV_NO of probe number PRO_NO
-- of core COR exists.
--
device {COR, PRO_NO, DEV_NO}
    PK {COR, PRO_NO, DEV_NO}

   FK {COR, PRO_NO} REFERENCES
probe {COR, PRO_NO}

-- Sensor number SNS_NO of device number DEV_NO,
-- of probe number PRO_NO, of core COR exists.
--
sensor {COR, PRO_NO, DEV_NO, SNS_NO}
    PK {COR, PRO_NO, DEV_NO, SNS_NO}

    FK {COR, PRO_NO, DEV_NO} REFERENCES
device {COR, PRO_NO, DEV_NO}

-- Channel CHN_NO of sensor number SNS_NO,
-- of device number DEV_NO, of probe number PRO_NO,
-- of core COR exists.
--
channel {COR, PRO_NO, DEV_NO, SNS_NO, CHN_NO}
     PK {COR, PRO_NO, DEV_NO, SNS_NO, CHN_NO}

    FK {COR, PRO_NO, DEV_NO, SNS_NO} REFERENCES
sensor {COR, PRO_NO, DEV_NO, SNS_NO}
  • Is this a bad design? No.
  • Is it logically sound? Yes.
  • What about normalization? 5NF (with ... attributes).
  • Is it OK for a physical implementation? Yes, maybe, no, depends.

Say that you are considering physical design and are concerned about width of keys and index sizes. At this point you decide to follow the natural hierarchy and have a single column identifier for each entity, even if it can not exist out of the context of another one.

-- Core COR exists.
--
core {COR}
  PK {COR}

-- Probe PRO of core COR exists.
--
probe {PRO, COR}
   PK {PRO}

   FK {COR} REFERENCES core {COR}

-- Device DEV of probe PRO exists.
--
device {DEV, PRO}
    PK {DEV}

    FK {PRO} REFERENCES probe {PRO}

-- Sensor SNS of device DEV exists.
--
sensor {SNS, DEV}
    PK {SNS}

    FK {DEV} REFERENCES device {DEV}

-- Channel CHN of sensor SNS, exists.
--
channel {CHN, SNS}
     PK {CHN}

     FK {SNS} REFERENCES sensor {SNS}

This one matches your initial design.

  • Is this a bad design? No.
  • Is it logically sound? Yes.
  • What about normalization? 5NF (with ... attributes).
  • Is this better? Yes, maybe, no, depends.

Make sure you do not allow NULLs. Allowing NULLs for FKs would basically answer "yes" to all "can X exist withoutY?" questions, and lead to completely different design (schema).

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key (Unique)
FK = Foreign Key
user_1967130

repeating the extra foreign keys is denormalized and will cause you trouble.

it should look like this:

Cores(id, fields...)
Probes(id, fields..., core_id)
Devices(id, fields..., probe_id)
Sensors(id, fields..., device_id)
Channels(id, fields..., sensor_id)

also - style points. your table names should be singular - the define one row of the contents. so Core, Probe, Device etc.

user_3187490
Related