Link to home
Start Free TrialLog in
Avatar of ltpitt
ltpitt

asked on

How to structure a small domotics database

Hello there.
I am building, for fun, a small domotics application for my home.

I am now planning its database and I can't solve a little problem I have with elegance.

I decided to do something similar (I use Python and SQLAlchemy but the suggestion I need is just conceptual):

from sqlalchemy import Column, ForeignKey, Integer, String

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import relationship

from sqlalchemy import create_engine

Base = declarative_base()


class Room(Base):
    __tablename__ = 'room'
    name = Column(
        String(80), nullable=False
    )

    id = Column(
        Integer, primary_key=True
    )


class Sensor(Base):
    __tablename__ = 'sensor'
    name = Column(
        String(80), nullable=False
    )
    id = Column(
        Integer, primary_key=True
    )
    room_id = Column(
        Integer, ForeignKey('room.id')
    )
    room = relationship(Room)

    @property
    def serialize(self):

        return {
            'name': self.name
        }

class SensorValues(Base):
    __tablename__ = 'sensor_values'

    temperature = Column(
        String(250)
    )
    humidity = Column(
        String(250)
    )



class Switch(Base):
    __tablename__ = 'switch'
    name = Column(
        String(80), nullable=False
    )
    id = Column(
        Integer, primary_key=True
    )
    type = Column(
        String(250)
    )
    description = Column(
        String(250)
    )
    room_id = Column(
        Integer, ForeignKey('room.id')
    )
    room = relationship(Room)

    @property
    def serialize(self):

        return {
            'name': self.name
        }

class Computer(Base):
    __tablename__ = 'menu_item'
    name = Column(
        String(80), nullable=False
    )
    id = Column(
        Integer, primary_key=True
    )
    ip = Column(
        String(250)
    )
    macaddress = Column(
        String(250)
    )
    room_id = Column(
        Integer, ForeignKey('room.id')
    )
    room = relationship(Room)

    @property
    def serialize(self):

        return {
            'name': self.name
        }




engine = create_engine(
    'sqlite:///domotica.db'
)

Open in new window


The problem I have is find a way to put sensor into the database.

Sensors can be of various type (temperature, light, humidity, etc) and therefore planning their table is quite complicated to me.
I could be sloppy and make a table for every type of sensor or a single table with used or unused fields depending on the sensor type but...

Is there a better idea to achieve this result?

Thanks for your help.
ASKER CERTIFIED SOLUTION
Avatar of gplana
gplana
Flag of Spain image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ltpitt
ltpitt

ASKER

Simple and clean.

Thanks for your help!
Avatar of ltpitt

ASKER

Sorry but my problem remains unsolved...

How should I handle a temperature - humidity sensor and a light sensor?

The 1st has, as values:
temperature, humidity

The 2nd has:
lux

I do not get how to enter this data using only a single "value" field in sensor_value table...
I don't see the problem.

In that case you have 2 sensors and 3 values:

These would be the inserts on database:

INSERT INTO type(type_id,name) VALUES (1,'temperature');
INSERT INTO type(type_id,name) VALUES (2,'humidity');
INSERT INTO type(type_id,name) VALUES (3,'lux');

INSERT INTO sensor(id, name, room_id) VALUES(1,'Sensor 1',404);
INSERT INTO sensor(id, name, room_id) VALUES(2,'Sensor 2',404);

INSERT INTO sensor_value(sensor_id, type_id, value) VALUES (1, 1, 25.7);
INSERT INTO sensor_value(sensor_id, type_id, value) VALUES (1, 2, 87,6);
INSERT INTO sensor_value(sensor_id, type_id, value) VALUES (2, 3, 3.5);

Does it makes sense for you?