0

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?

2
  • @Mat They contain very different data. This is about a Dungeons and Dragons campaign, so I have a table for creatures, for characters, for items, for diaryentries (session notes), for locations, for organizations etc. Characters can be in organizations, creature's, items and diaryentries don't. Item's can have owners, none of the others have that. Diaryentries can have authors, this is not true for any of the others. The list goes on. In these senses, they are wildly different records, it is merely in the general sense that I care about all 12 of these in general. Aug 14, 2021 at 12:53
  • @Mat Added some DDL statements to give example as to why these are separate tables. Aug 14, 2021 at 13:07

3 Answers 3

0

Think about your objects first.

You have something like :

Article 
{
    id
    name
    description
    creation
    update
}

And then:

LocationArticle : Article
{
    //extra fields
}

CharacterArticle : Article
{
    //extra fields
}

You can make your tables match these classes:

CREATE TABLE "Article" (
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "type" varchar(200) NOT NULL,
    "name" varchar(200) NOT NULL,
    "description" text NULL,
    "creation_datetime" datetime NOT NULL,
    "update_datetime" datetime NOT NULL
)

CREATE TABLE "LocationArticle" (
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "is_secret" bool NOT NULL,
    "parent_location_id" integer NULL REFERENCES "LocationArticle" ("id") DEFERRABLE INITIALLY DEFERRED
)

Now you can select the most recently edited article ids and type from the Article table in a single select, and LEFT JOIN where id=id to the other tables for the extra fields if required.

1
  • This makes a lot of sense! Thank you! I think for me that's a classic case of "application grew into not previously predicted thing and in its new shape you need to redesign the database". I think in terms of being clean overall, this is the correct response. I'll have to meditate over if I'd want to put in the effort for the redesign though, given that it changes how adding works since you now have to add an entry in 2 tables if you want to create an article as opposed to adding an entry in 1 table. Aug 14, 2021 at 13:48
3

The 12 different tables indeed sound a little fishy. But if you're somehow forced to use that design, you might implement a database view as a union of selects from the various tables, and run your queries against this view. Let the database engine figure out how to optimize this:

CREATE VIEW ALL_ARTICLES AS
SELECT CREATION_DATETIME,UPDATE_DATETIME,'TABLE1' FROM TABLE1
UNION
SELECT CREATION_DATETIME,UPDATE_DATETIME,'TABLE2' FROM TABLE2
UNION ...

In a realistic case, you would probably have SUBJECT, AUTHOR and ID columns so you could present search results nicely and access selected articles.

5
  • I am not quite forced to use the design, so I can change it. It is mostly that the tables truly do contain vastly different columns and thus different data. The way they "belong" together is more that I deem them all important enough to want to know as a user when they have been updated. A view is a great idea though! I am aware of them and used them before, just never with Django's ORM, which is why I initially dismissed that idea. A quick google search told me though that views can be connected to Django's ORM so the fact I didn't think of that is entirely on me. Thanks for the answer! Aug 14, 2021 at 13:21
  • Doesn't even need a view, just needs a SELECT query that performs the union across all tables, and then ORDER BY with a LIMIT. This will be efficient if the involved tables have an index on the update datetime column.
    – amon
    Aug 14, 2021 at 13:23
  • @amon That is true, Hans' approach does have 2 advantages though: 1) I can leverage Django's ORM with it, as Django models can be connected to view-tables (which I hadn't been aware of before). 2) If I have multiple occurrences for this kind of query I save myself the hassle in the future. Aug 14, 2021 at 13:25
  • have you any data on how performant it is to use top and union? I would assume that it would need to retrieve more records than required and then trim off the extra, but maybe sql optimises that away
    – Ewan
    Aug 14, 2021 at 13:46
  • I think that given appropriate indexes could yield acceptable performance, as the SQL engine can probably do a merge sort based on the indexed datetime fields. Aug 14, 2021 at 14:12
0

The answer provided by @Ewan is the one I have accepted, as at a larger scale it seems to me that it is the correct one. It models the data in the database in the way you'd also think about it, even though that makes handling it somewhat more complicated.

However, @Hans-Martin Mosner's approach also is very valid and in my case a lot less work to implement. As such, I went with that, created a view, put a Django model on top of it and created some helper functions that allowed me to fetch the original records via the ORM fairly easily.

Here my excerpt from Django on how I implemented this for future readers:

#mymodels.py
class ErroneousViewException(Exception):
    """Custom Error that is raised when the view V_ALL_ARTICLES turns out to contain incorrect information"""
    def __init__(self, faulty_record_id: int, table: str, message: str) -> None:
        self.faulty_record_id = faulty_record_id,
        self.table = table
        self.message = message
        super().__init__(message)

class AllArticlesView(models.Model):
    table_name = models.CharField(max_length=100, help_text="The name of the table that the record belongs to")
    record_id = models.PositiveIntegerField(help_text="The id of a record in the table with the given name")
    update_datetime = models.DateTimeField(db_index=True, help_text="The time the referenced record was last updated")
    guid = models.CharField(max_length=100, primary_key=True)

    class Meta:
        managed = False
        unique_together = ('table_name', 'record_id')
        db_table = "v_all_articles"

    def get_record_model(self) -> models.Model:
        (app_name, model_name) = self.table_name.split("_")
        return apps.get_model(app_name, model_name)
    
    def get_article_object(self) -> models.Model:
        article_model: models.Model = self.get_record_model()
        article_objects = article_model.objects.filter(pk=self.record_id)[0]

        if len(article_objects) == 0:
            raise ErroneousViewException(
                self.record_id,
                self.table_name,
                message=f"The view V_ALL_ARTICLES stored the record_id {self.record_id} of table {self.table_name} but no such record exists!"
            )

        if len(article_objects) > 1:
            raise ErroneousViewException(
                self.record_id,
                self.table_name,
                message=f"The view V_ALL_ARTICLES stored the record_id {self.record_id} of table {self.table_name}, which should be unique. It fetches more than 1 record though!"
            )

        return article_objects[0]


# views.py <-- Here we determine which subset of articles to query from the database
class RecentUpdatesPagination(APIView):
    queryset = mymodels.AllArticlesView.objects.all().order_by("-update_datetime")
    pagination_class = PageNumberPagination
    
    def get(self, request, page_number=0):
        page_size = settings.REST_FRAMEWORK["PAGE_SIZE"]
        page_start_index: int = page_size * page_number
        page_end_index = page_start_index + page_size

        article_references_on_page: QuerySet = self.queryset[page_start_index : page_end_index]
        articles: List[models.Model] = [article_reference.get_article_object() for article_reference in article_references_on_page]
        serialized_articles = [self.serialize_article(article) for article in articles]

        return Response(serialized_articles)

    def serialize_article(article):
        #A lot of logic for serialization

# create_all_articles_view.sql
create view v_all_articles as
    select 'wikientries_character' AS table_name,
        id AS record_id,
        update_datetime,
        'wikientries_character' || id AS guid
    FROM wikientries_character
    UNION ALL
    select 'wikientries_creature' AS table_name,
        id AS record_id,
        update_datetime,
        'wikientries_creature' || id AS guid
    FROM wikientries_creature
    UNION ALL
    select 'wikientries_diaryentry' AS table_name,
        id AS record_id,
        update_datetime,
        'wikientries_diaryentry' || id AS guid
    FROM wikientries_diaryentry
    UNION ALL
    select 'wikientries_encounter' AS table_name,
        id AS record_id,
        update_datetime,
        'wikientries_encounter' || id AS guid
    FROM wikientries_encounter
    UNION ALL
    select 'wikientries_item' AS table_name,
        id AS record_id,
        update_datetime,
        'wikientries_item' || id AS guid
    FROM wikientries_item
    UNION ALL
    select 'wikientries_location' AS table_name,
        id AS record_id,
        update_datetime,
        'wikientries_location' || id AS guid
    FROM wikientries_location
    UNION ALL
    select 'map_map' AS table_name,
        id AS record_id,
        update_datetime,
        'map_map' || id AS guid
    FROM map_map
    UNION ALL
    select 'wikientries_organization' AS table_name,
        id AS record_id,
        update_datetime,
        'wikientries_organization' || id AS guid
    FROM wikientries_organization
    UNION ALL
    select 'wikientries_quest' AS table_name,
        id AS record_id,
        update_datetime,
        'wikientries_quest' || id AS guid
    FROM wikientries_quest
    UNION ALL
    select 'wikientries_rules' AS table_name,
        id AS record_id,
        update_datetime,
        'wikientries_rules' || id AS guid
    FROM wikientries_rules
    UNION ALL
    select 'fileserver_sessionaudio' AS table_name,
        id AS record_id,
        update_datetime,
        'fileserver_sessionaudio' || id AS guid
    FROM fileserver_sessionaudio
    UNION ALL
    select 'wikientries_spell' AS table_name,
        id AS record_id,
        update_datetime,
        'wikientries_spell' || id AS guid
    FROM wikientries_spell;

4
  • Your original question was to find the most recent articles. Where does that ordering or limiting to n most recent occur in this code example? As I read it the code might retrieve all articles in an undefined order. The undefined order might happen to be the order you want, until you change something.
    – joshp
    Aug 14, 2021 at 19:46
  • 2
    The UNION query approach is solid. As long as the update time is indexed in the underlying tables you can go far with this. However, for most RDBMS, you would be better off using "UNION ALL". UNION (a set operation) requires SQL to guarantee no duplicate rows in the result. This often results in an extra sort/remove duplicates operation. Your underlying queries already guarantee unique results, but the optimizer is unlikely to figure that out. This is a common mistake, easy to fix. UNION ALL means don't insure uniqueness, return all rows. As data grows the difference can be quite significant.
    – joshp
    Aug 14, 2021 at 19:59
  • 1
    @joshp 1) Where is ordering + limiting ? - Fair point! I added a shortened version of a Django view I'm using for the serialization and pagination. The ordering happens there by defining the queryset with an order_by("-update_datetime") and limiting by taking a slice of the queryset. 2) UNION: That's a fair point, I adjusted the query accordingly. Aug 14, 2021 at 21:08
  • "short cuts mean long delays" Gandalf
    – Ewan
    Aug 15, 2021 at 8:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.