An IoT client and MVC-based server. Data model with different fields per type?

Yii 2.0 Tips, Snippets and Tutorials
1

The problem

Let’s assume the following scenario:

  • List of IoT devices (one model?)
  • Each list item has (among other fields) a type field
  • Depending on the value in the type field, device may report different values (by number and sometimes type)

And I need to save them in the database (related to reporting device, of course).

So for example:

  • type = 1 (thermometer): only one parameter:
    • temperature (INT – max. 99 + comma + two decimal places)
  • type = 2 (GPS locator): four parameters:
    • lat, long (INT – max 999 + comma + 6 decimal places),
    • height (INT – max 99999, without fractional part),
    • speed (INT – max 999 + comma + 2 places).

Etc.

These are of course examples, not an actual case.

I need to model such thing in MVC. Assuming (best of both worlds):

  • It should be relatively easy to implement (the project behind is way off time),
  • It should be done at least semi-professionally (one day a new project can be created based on this code and we don’t want to rewrite 300 classes from scratch, if that happens).

Can you suggest something around the above?

This is not a “homework” or do-it-for-me question. I don’t need a mature, out-of-the-box solution, only guiding into a correct direction.

An attempt to resolve

I am by no means system architect, I don’t know design patterns very well. So the only thing that came to my mind so far was the simplest concept of two models or tables:

  • devices:
    • id
    • type
    • etc.
  • values:
    • id
    • device_id
    • param_type
    • value

But this seams very redundant (storing the type in each measurement?).

Plus, it only covers data storage itself takes. It does nothing around “configuration” of these parameters (input validation).

For example, how to guarantee that:

  • A device with type = 1 will report only one parameter (and only of a specific type)
  • A device with type = 2 will report exactly four parameters and always of the specified types

And any other attempt will be rejected.

To resolve this, at the very beginning I was thinking about using separate models. But this seems to be another overkill. As there may be hundreds of types of devices. Which would inflict hundreds of such models.

Own research

This question is somewhat similar to this one. Only mine deals with model / data storage and the mentioned one is purely focused on presentation layer (which isn’t a problem for me).

My question is also quite similar to this one. But the mentioned one isn’t answered for three years and I have the feeling that I have come a bit further here.

2

if this part is completely dynamic then you need to provide a way to define parameter and it’s rules when user creates device. I don’t much of context of your project but you have similar rule set as your example then you can come up with rules table to save them and whenever user selects the device, you can load parameters and generate rules dynamically.

3

Thanks for trying to help, but it seems that you are looking at this at the too high level.

I am aware of everything that you have just wrote. Yes, you are totally correct that I’ll have to provide my users with everything that you listed, including defining parameters and rules.

But these are all business-level / design-level issues. And right now, I need to start from the very scratch.

  1. How to store this information in database?
  2. What would be the suggested model relation / database structure?

Is my suggested structure anywhere close to reliable? Should I push forward and try to store all devices’ data (irresponsive of type) in single database structure?

Or should I rather reject the whole idea and create a separate model / separate table for each device type (and data structure)? And have each table’s column structure exactly matching given device type.

To make things even simpler: Should I store both temperature from thermometer and GPS data from GPS locator in the same table or tables structure (like I proposed)? Or is this a way to nowhere and I should store temperature in one table and GPS data in another (and so on… and so on… ending with hundred tables for hundred different device types)?

I know that you are not aware of a context of my project, but my question is as high-level as: Am I able to store different data types / structure in a single table / table structure? Or should I rather have separate table for each separate data structure.

The last one seems obvious, but that vision of having 300+ tables for 300+ device types simply overwhelms me.

4

Making different tables for each device is not a feasible idea. Table structure looks ok from what you mentioned.

5

The tables structure you propose is quite good and relevant.

You can have two ActiveRecord classes for your tables. For ‘devices’ it’s pretty straighforward one but for ‘values’ extended one. I skip the ‘device[1]->[n]values’ relation, it is obvious it should be there.

Let’s focus on ‘values’. I would store value, independent of its type as string, casting to it for simple types (e.g int, double) but you can even serialize arrays this way. This way you can have one and only simple rule ‘string’ for the value. And that would be all for the base class directly coupled with the table. Next I would extend it for every distinct value type I need. In the child class you can parse string and cast value to any type you want. The ‘param_type’ decides then on how to manage and what you can do with ‘value’. Of course it should works reverse way for saving value of particular type as string. This way you can have quite light child classes, focused only on handle one parameter type (one for integer, one for decimal etc.).
If the particular type is subject of being used as input in user form you can have a static function returns custom validator class to use in the form.

6

Thank you for all your valuable ideas and solutions. This has certainly pointed me into the correct direction.

I don’t intend to keep this discussion longer. This is part of my private project and thus any further information would rather be useful only to me, not to the community.

Thank you again, I know now what I wanted to know.

7

You might want to explore EAV

1 Like
8

Sound like a great idea to explore! Thanks!

Are you aware of any ready PHP or Yii 2 implementation (to not reinvent the wheel)?

Or must this be implemented from scratch?

9

There are some of Yii2 extension. Checkout packagist and play around.
Let us know if you find any useful. Here is the link:

10

Be aware that there are many articles in the Internet about why EAV model is bad. It has pitfails if used without moderation. What I have proposed is also EAV of some kind, but in that simple usage, only for determined variable set of value types it shouldn’t be that bad.

1 Like
11

Nothing is good in software Engineering without moderation.

2 Likes