SQLAlchemy: foreignKeys from multiple Tables (Many-to-Many)

Question

I'm using flask-sqlalchemy orm in my flask app which is about smarthome sensors and actors (for the sake of simplicity let's call them Nodes.

Now I want to store an Event which is bound to Nodes in order to check their state and other or same Nodes which should be set with a given value if the state of the first ones have reached a threshold.

Additionally the states could be checked or set from/for Groups or Scenes. So I have three diffrent foreignkeys to check and another three to set. All of them could be more than one per type and multiple types per Event.

Here is an example code with the db.Models and pseudocode what I expect to get stored in an Event:

db = SQLAlchemy()

class Node(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    value = db.Column(db.String(20))
    # columns snipped out

class Group(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    value = db.Column(db.String(20))
    # columns snipped out

class Scene(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    value = db.Column(db.String(20))
    # columns snipped out


class Event(db.Model):
    id = db.Column(db.Integer, primary_key=True)

    # The following columns may be in a intermediate table
    # but I have no clue how to design that under these conditions

    constraints = # list of foreignkeys from diffrent tables (Node/Group/Scene)
                  # with threshold per key

    target = # list of foreignkeys from diffrent tables (Node/Group/Scene)
             # with target values per key

In the end I want to be able to check if any of my Events are true to set the bound Node/Group/Scene accordingly.

It may be a database design problem (and not sqlalchemy) but I want to make use of the advantages of sqla orm here.

Inspired by this and that answer I tried to dig deeper, but other questions on SO were about more specific problems or one-to-many relationships.

Any hints or design tips are much appreciated. Thanks!

score 0 · Accepted Answer · answered Apr 01 '21 at 12:08

I ended up with a trade-off between usage and lines of code. My first thought here was to save as much code as I can (DRY) and defining as less tables as possible.

As SQLAlchemy itself points out in one of their examples the "generic foreign key" is just supported because it was often requested, not because it is a good solution. With that less db functionallaty is used and instead the application has to take care about key constraints.

On the other hand they said, having more tables in your database does not affected db performance.

So I tried some approaches and find a good one that fits to my usecase. Instead of a "normal" intermediate table for many-to-many relationships I use another SQLAlchemy class which has two one-to-many relations on both sides to connect two tables.

class Event(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    noodles = db.relationship('NoodleEvent', back_populates='events')
    # columns snipped out

    def get_as_dict(self):
        return {
            "id": self.id,
            "nodes": [n.get_as_dict() for n in self.nodes]
        }


class Node(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    value = db.Column(db.String(20))
    events = db.relationship('NodeEvent', back_populates='node')
    # columns snipped out


class NodeEvent(db.Model):
    ev_id = db.Column('ev_id', db.Integer, db.ForeignKey('event.id'), primary_key=True)
    n_id = db.Column('n_id', db.Integer, db.ForeignKey('node.id'), primary_key=True)
    value = db.Column('value', db.String(200), nullable=False)
    compare = db.Column('compare', db.String(20), nullable=True)
    node = db.relationship('Node', back_populates="events")
    events = db.relationship('Event', back_populates="nodes")

    def get_as_dict(self):
        return {
            "trigger_value": self.value,
            "actual_value": self.node.status,
            "compare": self.compare
        }

The trade-off is that I have to define a new class everytime I bind a new table on that relationship. But with the "generic foreign key" approach I also would have to check from where the ForeignKey is comming from. Same work in the end of the day.

With my get_as_dict() function I have a very handy access to the related data.

Maybe someone could improve this answer with `GroupEvent(db.Model)` etc. under the use of inheritance (where just the `n_id` field gets overwritten) — Michael P, Apr 01 '21 at 12:14

SQLAlchemy: foreignKeys from multiple Tables (Many-to-Many)

1 Answers1