Separate primary key in multi-table inheritance situations

Created on Wednesday 15 July 2020, 14:35

Back to task list
  • ID
    33488
  • Project
    Metabolism of Cities
  • Status
    Open
  • Priority
    Medium
  • Type
    Programming work
  • Assigned to
    No one yet
  • Subscribers
    Paul Hoekman

You are not logged in

Log In Register

Please join us and let's build things, together!

Description

Yeah OK so this is quite a thing. When I first set up the main table layout, I used multi-table inheritance for nearly all tables. We have a basic table (called records), which has some key fields like name, description, and most importantly the linkages with other tables like the associated reference spaces, sectors, or tags. All good so far.


However, when setting up this structure I didn't define a primary key for each table and instead let Django do its thing and use the original record_id as the primary key. That works well but the main disadvantage is that now every single table ID will follow a single sequence. So the first dataset could be ID 38723 because there are thousands of chat messages, news items, reference spaces or photos in the system. It's not a deal breaker but it's a bit inconvenient that the numbers blow up so quickly when people want to refer to the datasets in external places. It would be much neater if they can refer to dataset 392 or 238 instead of 478373.


To set this up, we need to do this in two phases:


First we add these fields to a table:


> uid = models.IntegerField(null=True, blank=True)
> record_id = models.IntegerField(null=True, blank=True)


Then we must update the records with something like this:


> check = LibraryItem.objects_unfiltered.all().order_by("uid")[0]
> uid = check.uid if check.uid else 0
> pending = LibraryItem.objects_unfiltered.all()
> for each in pending:
> each.record_id = each.id
> uid += 1
> each.uid = uid
> each.save()


And then we update the model:


> uid = models.AutoField(primary_key=True)
> record_id = models.OneToOneField(
> Record, on_delete=models.CASCADE,
> parent_link=True,
> primary_key=False,
> )


The problem, however, is that this can't be done just like that. Django migrations will fail because the existing foreign keys are used elsewhere. So we must manually go through the tables, break the relationships with other tables, then update the fields, and then re-establish the foreign keys.


It's tedious and no fun. But it would be great to sort this out before we go live for real. Otherwise we will also have to deal with redirecting URLs and/or lots of 404s.

Discussion and updates


New task was created