Relational Data Structures
In an RDBMS data is structured to mimic the entity, attribute, and relationship concepts using the following structures:
- Tables, Columns, and Rows
- Primary Keys and Foreign Keys
Tables, Columns, and Rows
Every entity generally has an associated Table. A table has Columns or Fields which represent the attributes of the entity. And a table has multiple Rows where each row represents a different entity.
In the online ecommerce example, there would be a table for Customers, Items, and Banks. In the IoT example, there would be a table each for Sensors, Microcontrollers, and Servers. Each table would have columns that would hold a value for the entity attributes. And each Customer, Item, Bank, and so on would have a row for itself with its attributes.
The columns (fields) in a table have data types similar to those used in high-level languages. When a field is assigned a data type, it can only store values of that data type. SQL commands also use the same data types. Fields can be mandatory (they must have some value) or can be optional (blank or null).
Examples of Tables:
Sensors Table
| sensor_id (number, unique, mandatory) | sensor_name (string, mandatory) | sensor_type (string, mandatory) |
|---|---|---|
| 1 | DHT11 | Discrete |
| 2 | MQ Series | Binary |
Parameters Table
| parameter_name (string, unique, mandatory) | minimum_value (number, mandatory) | maximum_value (number, mandatory) |
|---|---|---|
| Temperature | 0 | 40 |
| Humidity | 20 | 100 |
| Gas | 0 | 1 |
Servers Table
| server_ip (string, unique, mandatory) | server_port (number, mandatory) | server_name (string, mandatory) |
|---|---|---|
| 127.0.0.1 | HTTPS Server | 443 |
| 192.168.0.1 | MQTT Server | 1883 |
Entity Relationships
In a relational database, each record in a table has a unique identifier, known as the primary key (PK). The primary key can be a system-generated unique number or one of the attributes of the entity and assumes that it can be guaranteed to be unique across all the entities in the application (all the rows in a table).
Tables are designed such that data is stored in what is known as a normalized form, such that there is no duplication of data values. The relationship between entities can take multiple forms in terms of number of entities in the relationship:
-
One-to-Many:
This is a relationship where one entity instance is related with multiple instances of another entity. For example, one sensor can measure multiple parameters. -
Many-to-Many:
This is a relationship where multiple instances of one entity are related with multiple instances of another entity. For example, one sensor can send data to multiple servers and one server can have multiple sensors sending data to.
One-to-Many Relationship Table
Each sensor can capture values for one or more parameters but each parameter is captured by only one sensor. In this relationship table the primary keys of both tables are linked together.
| sensor_id | parameter_name |
|---|---|
| 1 | Temperature |
| 1 | Humidity |
| 2 | Gas |
Database design is a fairly complex topic so we have only covered some basic concepts which will be enough for our projects.