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.