I have an Angular + Django web application in which Django functions as my backend.
In this web application, I have 12 different tables about articles, each table representing a different kind of article, which means they contain vastly different columns at times. They all have the fields creation_datetime
and update_datetime
but are otherwise very dissimilar.
Example:
//SQLite DDL for character table
CREATE TABLE "wikientries_character" (
"id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
"player_character" bool NOT NULL,
"alive" bool NOT NULL,
"name" varchar(200) NOT NULL UNIQUE,
"gender" varchar(10) NOT NULL,
"race" varchar(50) NOT NULL,
"title" varchar(200) NULL,
"description" text NULL,
"is_secret" bool NOT NULL,
"current_location_id" integer NULL REFERENCES "wikientries_location" ("id") DEFERRABLE INITIALLY DEFERRED,
"organization_id" integer NULL REFERENCES "wikientries_organization" ("id") DEFERRABLE INITIALLY DEFERRED,
"creation_datetime" datetime NOT NULL,
"update_datetime" datetime NOT NULL
)
//SQLite DDL for location Table
CREATE TABLE "wikientries_location" (
"id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
"name" varchar(200) NOT NULL,
"description" text NULL,
"is_secret" bool NOT NULL,
"creation_datetime" datetime NOT NULL,
"update_datetime" datetime NOT NULL,
"parent_location_id" integer NULL REFERENCES "wikientries_location" ("id") DEFERRABLE INITIALLY DEFERRED
)
I want to fetch the X most recently updated articles across all 12 tables, so the top X entries if ordered by update_datetime
.
The naive approach to doing this is fetching all entries across all 12 tables (which is at least 12 queries) into your application server, putting them all in a massive list/array, sorting by update_datetime
and then taking the top X entries. This is what I have implemented currently (I have a small database at the moment), but I don't think that is viable for larger datasets because it requires fetching a lot of data from the database multiple times.
The only other approach I can think of, is the following:
- Create new table
Recently Updated Articles
that references an article across the 12 tables. To do so it would have the columns "table_name" (varchar of the table the record is in) and "pk" (the primary key of the specific record on the table). It shall also have the column "update_date" which is the value Recently Updated Articles
shall never be manipulated directly by any view, only read from- Implement triggers on my application server (Django Signals) that update
Recently Updated Articles
whenever a Create, Delete or Update operation happens on one of my 12 tables.
I could then get references to the X most recently updated articles by just querying Recently Updated Articles
and then fetching content of those referenced articles from my 12 tables.
What's stopping me from doing this is that it seems... ugly. But I'm also not quite seeing a better way of doing this. Is there a better solution for this kind of problem?