[Django]-Storing multiple values into a single field in mysql database that preserve order in Django

by

3đź‘Ť

âś…

Here entries in this series field are post_ids stored as a string representation of a list.
(…)
So I just read the series field and get all the Posts with the ids in this list then display them using pagination in Django.

DON’T DO THIS !!!

You are working with a relational database. There is one proper way to model relationships between entities in a relational database, which is to use foreign keys. In your case, depending on whether a post can belong only to a single tutorial (“one to many” relationship) or to many tutorials at the same time (“many to many” relationship, you’ll want either to had to post a foreign key on tutorial, or to use an intermediate “post_tutorials” table with foreign keys on both post and tutorials.

Your solution doesn’t allow the database to do it’s job properly. It cannot enforce integrity constraints (what if you delete a post that’s referenced by a tutorial ?), it cannot optimize read access (with proper schema the database can retrieve a tutorial and all it’s posts in a single query) , it cannot follow reverse relationships (given a post, access the tutorial(s) it belongs to) etc. And it requires an external program (python code) to interact with your data, while with proper modeling you just need standard SQL.

Finally – but this is django-specific – using proper schema works better with the admin features, and with django rest framework if you intend to build a rest API.

wrt/ the ordering problem, it’s a long known (and solved) issue, you just need to add an “order” field (small int should be enough). There are a couple 3rd part django apps that add support for this to both your models and the admin so it’s almost plug and play.

IOW, there are absolutely no good reason to denormalize your schema this way and only good reasons to use proper relational modeling. FWIW I once had to work on a project based on some obscure (and hopefully long dead) PHP cms that had the brillant idea to use your “serialized lists” anti-pattern, and I can tell you it was both a disaster wrt/ performances and a complete nightmare to maintain. So do yourself and the world a favour: don’t try to be creative, follow well-known and established best practices instead, and your life will be much happier. My 2 cents…

👤bruno desthuilliers

3đź‘Ť

I can think of two approaches:

Approach One: Linked List

One way is using linked list like this:

class Tutorial(models.Model):
    ...
    previous = models.OneToOneField('self', null=True, blank=True, related_name="next")

In this approach, you can access the previous Post of the series like this:

for tutorial in Tutorial.objects.filter(previous__isnull=True):
   print(tutorial)
   while(tutorial.next_post):
      print(tutorial.next)
      tutorial = tutorial.next

This is kind of complicated approach, for example whenever you want to add a new tutorial in middle of a linked-list, you need to change in two places. Like:

post = Tutorial.object.first()
next_post = post.next
new = Tutorial.objects.create(...)
post.next=new
post.save()
new.next = next_post
new.save()

But there is a huge benefit in this approach, you don’t have to create a new table for creating series. Also, there is possibility that the order in tutorials will not be modified frequently, which means you don’t need to take too much hassle.

Approach Two: Create a new Model

You can simply create a new model and FK to Tutorial, like this:

class Series(models.Model):
    name = models.CharField(max_length=255)


class Tutorial(models.Model):
   ..
   series = models.ForeignKey(Series, null=True, blank=True, related_name='tutorials')
   order = models.IntegerField(default=0)


   class Meta:
      unique_together=('series', 'order')  # it will make sure that duplicate order for same series does not happen

Then you can access tutorials in series by:

series = Series.object.first()
series.tutorials.all().order_by('tutorials__order')

Advantage of this approach is its much more flexible to access Tutorials through series, but there will be an extra table created for this, and one extra field as well to maintain order.

👤ruddra

Leave a comment