The Smart Home Data Model

Smart homes used to be strictly in the future; now they are a reality. Most of us have heard about them, but they are not so widespread as they will be in the near future. Managing your home the ‘smart’ way will definitely produce a lot of data. Today, we’ll analyze a data model we could use to store smart home data.

The Data Model

When you think of a smart home, you probably think of remotely locking and unlocking your home, activating alarms, lights, or cameras from your phone, having thermometers that automatically manage your heating and cooling, etc. But smart homes can do much more. You can connect a number of smart devices and controllers to achieve many complex functionalities. You can send instructions to devices or read their statuses from wherever you are.

Let’s take a look at a data model that would allow us to implement such functionalities. On top of that data model, we could build a web application and a mobile application that would allow registered users to administer their accounts, send instructions, and track statuses.

The model consists of three subject areas:

• Estates & devices
• Users & profiles
• Commands & data

I’ll describe each of these subject areas in the order they are listed. Before anything else, though, I’ll describe the user_account table.

The User_Account Table

We’re starting with the user_account table because it’s used in all three subject areas. It stores a list of all the registered users of our application.

The user_account table contains all the standard attributes you would expect, including:

• first_name and last_name – The first and last name of the user.
• user_name – A UNIQUE username chosen by the user.
• password – A hash value of the user’s password.
• email– The email address provided by the user during the registration process.
• confirmation_code – The code generated during the registration process.
• confirmation_time – The timestamp when the user confirmed their account and completed the registration process.
• ts – The timestamp when this record was inserted into the database.

Section 1: Estates and Devices

The whole motivation behind creating this database is to monitor what is happening with our estates(i.e. homes or properties). These could be private estates, like apartments or houses, or they could be business properties, like offices, warehouses, etc. If we truly want to push our system to the limit, we could also use it for vehicles.

The central table in this subject area is the real_estate table. This is where we’ll store all the different estates or properties connected to our smart home app. For each estate, we’ll store:

• real_estate_name – A name, chosen by the user, that refers to a specific property.
• user_account_id – The ID of the user this estate is related to. Together with the real_estate_name attribute, this forms the UNIQUE (alternate) key of this table.
• real_estate_type – Denotes the type of real estate this property is.
• address – The actual address of that estate. This is nullable because we might use this system for other types of property (i.e. vehicles).
• details – All additional details in textual format.

We’ve already mentioned real estate types. A complete list of possible types is stored in the real_estate_type dictionary. It contains only one UNIQUE value, the type_name. We could expect values like “apartment”, “house”, or “garage” here.

One piece of real estate can be divided into several areas. This could be rooms of an apartment or a house; maybe we want to group a few rooms together or we want everything related to the yard or garden in one group, etc. Or maybe we have an industrial estate or complex with several offices; if our property is really large, having specific areas can be very useful. To achieve that, we’ll use the area table. It contains the UNIQUE pair of the real_estate_id and the area_name chosen by the user.

The last two tables in this subject area are related to devices.

In the device_type table, we’ll store a complete list of all distinct device types. For each type, we’ll use a UNIQUE type_name and insert an additional description if needed. Device types could be different sensors (temperature, gas), door or window locks, lights, air conditioning and heating systems, etc.

Now we’re ready for the fun part. We’ll use the device table to store a list of all the devices included in various smart homes. These devices are added by the user, either manually or automatically if the device has that option. For each device, we’ll need to store:

• real_estate_id – The ID of the real estate where this device is installed.
• area_id – References the area where this device is installed. This is an optional value because an estate could be divided into areas, but it may not be divided, either.
• device_type_id – The ID of the device_type this device belongs to.
• device_parameters – We’ll use this attribute to store all possible device parameters (e.g. intervals for storing data) in a structured textual format. These parameters could be set by the user or a part of the device’s regular function (e.g. different measures).
• current_status – The current status of device parameters.
• time_activated and time_deactivated – Denote the interval when this device was active. If time_deactivated is not set, then the device is active and the is_active attribute is also set to True.

Section 2: Users and Profiles

We’ve already mentioned the user_account table. In our application, users should be able to create different profiles and share them with other users if they want to.

Profiles are basically subsets of devices installed in every piece of real estate owned by the user. Each user could have one or more profiles. The idea is to enable a user to group their smart home devices in a suitable way. While the user could have one profile with all of their devices, they could also have one profile showing only the front-door cameras for all their properties. Or maybe one profile just for the thermometers installed in all the rooms of their apartment.

All profiles created by users are stored in the profile table. For each record, we’ll have:

• profile_name – The profile’s name, chosen by the user.
• user_account_id – References the user who created this profile. This attribute and the profile_name attribute form the UNIQUE key of the table.
• allow_others – A flag denoting if this profile is shared with other users.
• is_public – A flag denoting if this profile is visible publicly. Although we can expect this will be set to False most of the time, there could be cases when we want to share a profile with everyone.
• is_active – A flag denoting if this profile is active or not.
• ts – A timestamp of when this record was inserted.

For each profile, we’ll define a list of all devices included in it. This list is stored in the profile_device_list table and contains a list of UNIQUE profile_id – device_id pairs. This foreign key pair forms the primary key of our table.

The last table in this subject enables users to share their profiles with other registered users. This could be very useful, e.g. if one person manages everything and other registered users (e.g. family members) want to view profiles created by the owner. In the shared_with table, we’ll store a list of all UNIQUE pairs of profile_id – user_account_id. Once again, this foreign key pair is the primary key of the table. Please note that sharing will work only if the profile.allow_others attribute is set to True.

Section 3: Commands and Data

We’ll use tables from this last subject area to store communications between users and devices. This will be a two-way communication. Devices will generate the data during their operations and our database will store it as well as any messages generated by the system (or the devices). Users will want to see these messages and the data sent by their devices. Based on this data, users could create programs for their smart home. These programs are manually or automatically generated commands or even a series of commands that will do exactly what each user wants.

We’ll start with the data devices give us. In the device_data table, we’ll store a description of device-generated data and the location(s) of the data. Again, data is automatically generated by devices. It could be a series of measurements, statuses (textual data), or audio-visual data. For each record in this table, we’ll store:

• device_id – The ID of the device that generated this data.
• data_description – The description of the stored data, e.g. what is stored, when the data was stored in our system, and in what format.
• data_location – The full path to the location where this data is stored.
• ts – The timestamp when this record was inserted.

Device data will be stored regardless of whether the device is functioning properly or if the data is out of the expected range. This is basically a log of everything that was recorded by the devices. We can expect to have a strategy for deleting old device data files, but that’s out of the scope of this article.

Unlike device data, we can expect that messages will only be generated when something unexpected happens – e.g. if a device measurement is outside of the normal range, if a sensor detected movement, etc. In such cases, the device generates the messages. All such messages are stored in the auto_message table. In each record, we’ll have:

• device_id – The ID of the device that generated this message.
• message_text – The text generated by the device. This could be a predefined text, a value that is out of the expected range, or a combination of these two.
• ts – A timestamp of when this record was stored.
• read – A flag denoting if the message had been read by the user.

The remaining three tables are used to store users’ commands. Commands allow users to take control of their controllable devices and establish two-way communication with their smart home.

First, we’ll define a list of all possible command types. These could be values like “turn on/off”, “increase/decrease temperature”, etc. We could also have conditional commands,i.e. commands that are only fulfilled if a specific condition is true. A list of all distinct command types is stored in the command_type dictionary. For each type, we’ll store a UNIQUE type_name. We’ll also store a list of all parameters that should be defined for that type of command. This will be in a structured text format with inserted default values. The user will set these values when inserting their new commands.

A user could also define all recurring_commands up front. Maybe we want hot water each day at 7 AM or to activate the heating/cooling system each day at 4 PM. The set of rules and all needed attributes to make recurring commands happen are stored in this table. We’ll have:

• user_account_id – The ID of the user who inserted this recurring command.
• device_id – The ID of the device relevant to this recurring command.
• command_type_id – A reference to the command_type dictionary.
• parameters – All the parameters that should be defined for that command type, together with their desired values.
• interval_definition – The interval between two recurrences of this command. As with command parameters, this will be a structured text field.
• active_from and active_to – The interval during which this command should be repeated. The active_to attribute can be NULL if we want that command to repeat forever (or until we define the active_to date).
• ts – The timestamp when this record was inserted.

The last table in our model contains a list of single commands. These commands could be inserted manually by the user or generated automatically (i.e. as part of recurring commands). For each command, we’ll store:

• recurring_command_id – The ID of the recurring command triggering this command, if any.
• user_account_id – The ID of the user who inserted this command.
• device_id – The ID of the relevant device.
• command_type_id – References the command_type dictionary.
• parameters – All the parameters relevant to this command.
• executed – A flag denoting if this command has been executed.
• ts – The timestamp when this record was inserted.

What Do You Think of the Smart Home Data Model?

In this article, we’ve tried to cover the most important aspects of managing a smart home. One of them is definitely two-way communication between the user and the system. Most “real” actions are stored as textual parameters, and these values should be parsed when performing specific actions. This was done so we could have enough flexibility to work with many different device types.

Do you have any suggestions to add to our smart home model? What would you change or remove? Please tell us in the comments below.