Next we will need to create the database structure that represents the hardware. This will include the hub, gateway, node, and sensors. As devices (gateways, nodes, sensors) are added (or provisioned) to the system these database entries will be automatically created. Below we have the following hardware structure.
With this information we can consider a possible database structure to store this data. The below database structure is proposed to store the necessary information. Just as before we have the id, uuid, memo, date/time stamps, and status field. We have added one more field for each device for its hardware id (hubid, gatewayid, nodeid, actuatorid, sensorid). This will the data unique to each piece of hardware similar to how each computer on the network has a globally unique ethernet MAC address. This will be important later to identify and track the hardware and route the data to the correct place.
When it comes time to link the group-site-location information we can link the location_actuator/location_sensor to node_actuator/node_sensor records through a many-to-many relationship. The following two tables will allow the many-to-many record linking.
We have created quite a few tables but with these tables in addition to the tables in part 1 we should have constructed the necessary data structure for our IoT platform. We have not yet taken into consideration usernames, passwords, permissions, and security. We will come back to address this later to restrict how data can be linked and edited. With the database structure decided we can now start creating our code on the server to access and store information in the database.
Concerning the naming convention, yes we can use simple names for the data without appending the table name on to the field name, but from prior experience it soon becomes a difficult task to keep track of which fields belong to which database when creating complicated queries. For this reason the table name is appended as a prefix to each field name. There is no reason why it is necessary except it helps with keeping things organized.
Our next logical task will be looking at how to get the data from the sensor to the server and then create the necessary parts in the server to parse and store this data.