I'm no expert in DBMS and trying to build up an alerting system that can attach itself to many tables, so I figured asking the question would help my architecture.
The thing that may confuse me a little is that you can have multiple alerts attached to each model (sessions, events, organization) that are represented in tables in my database. One record of each model can have multiple different alerts ringing up.
The simplest way to solve this is to create a table such as
CREATE TABLE alerts (
id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
alert_type character varying(50) NOT NULL,
metadata jsonb,
session_id uuid REFERENCES sessions(id) ON DELETE DO NOTHING ON UPDATE CASCADE,
event_id uuid REFERENCES events(id) ON DELETE DO NOTHING ON UPDATE CASCADE,
organization_id uuid REFERENCES organizations(id) ON DELETE DO NOTHING ON UPDATE CASCADE,
created_at timestamp without time zone,
updated_at timestamp without time zone
);
But it seems to be a bad strategy if we grow that to many more tables attached to the alerting system. It obviously doesn't scale well. I'm hesitating between 2 strategies.
You have a table alerts and to represent the many-to-many a second table alerts_relations that has table_name and table_id to make sense of each other model/table it's attached to.
CREATE TABLE alerts (
id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
alert_type character varying(50) NOT NULL,
metadata jsonb,
created_at timestamp without time zone,
updated_at timestamp without time zone
);
CREATE TABLE alerts_relations (
id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
alert_id uuid REFERENCES alerts(id) ON DELETE CASCADE ON UPDATE CASCADE,
table_name character varying(50) NOT NULL,
table_id character varying(50) NOT NULL,
created_at timestamp without time zone,
updated_at timestamp without time zone
);
The other strategy removes table_name and table_id and replace it with the following
CREATE TABLE session_alerts (
session_id uuid REFERENCES sessions(id) ON DELETE CASCADE ON UPDATE CASCADE
) INHERITS (alert_relations);
CREATE TABLE event_alerts (
event_id uuid REFERENCES events(id) ON DELETE CASCADE ON UPDATE CASCADE
) INHERITS (alert_relations);
CREATE TABLE organization_alerts (
organization_id uuid REFERENCES organizations(id) ON DELETE CASCADE ON UPDATE CASCADE
) INHERITS (alert_relations);
I'm wondering what strategy yields better performance and what's the easiest to maintain. If that helps, my codebase is in Golang, so I've to work through the SQL manually and do little ORM work.
The last strategy seems to be the most convenient to my business logic because each relationship can be represented with a different table and name. Sounds easier to maintain, but I'm not sure about anything at this point; there may be drawbacks I don't see.
Any other solution is welcome of course! Thanks for reading.
