MariaDB views: I want to replace repeated multi-table joins in my queries with a view – are there any issues to watch out for?

Rather than

SELECT a.pk, b.pk, c.pk, d.name  FROM a JOIN b on b.pk = a.fk JOIN c on c.pk = b.fk JOIN d on d.pk = c.fk 

I can do

SELECT a_pk, b_pk, c_pk, d_name  FROM view_a_b_c_d 

I have a lot of this sort of thing through my code.

I’ve done a performance test, and the differences seem to be negligible, and I feel it would greatly tidy my codebase up and remove a lot of repetition.

But before I commit to that (as it’d be a big change with a lot of work and testing), I want to check that this IS a good thing to do. I didn’t study computer science and have no formal DBA training. I’m also a sole dev working on my own closed-source product. So I don’t get much input from the outside world, unless I strike out and ask for it.

Thank you – any opinions/experience appreciated.

Which pattern to use for a model to have different variations of? Generic FK, multi-table, inheritance, others?

I am having trouble deciding how to structure my models for a particular data structure.

The models I have would be Posts, Groups, Users.

I want the Post model that can be posted from a groups page or user page and potentially more, like an events page.

Posts would contain fields for text, images(fk), user, view count, rating score (from — a reference to where ever it was posted from like user or group page, though I am unsure how to make this connection yet)

I thought about using a Generic Foreign Key to assign a field to different models but read articles suggesting to avoid it. I tried the suggested models, but I wasn’t unsure if they were the right approach for what I required.

At the moment I went with Alternative 4 – multi-table inheritance

class Group(models.Model):     name = models.CharField(max_length=64)     created_by = models.ForeignKey(         settings.AUTH_USER_MODEL, on_delete=models.CASCADE, related_name='_groups')     members = models.ManyToManyField(         settings.AUTH_USER_MODEL)      def __str__(self):         return f'{self.name} -- {self.created_by}'      def save(self, *args, **kwargs):         # https://stackoverflow.com/a/35647389/1294405         created = self._state.adding         super(Group, self).save(*args, **kwargs)         if created:             if not self.members.filter(pk=self.created_by.pk).exists():                 self.members.add(self.created_by)   class Post(models.Model):     content = models.TextField(blank=True, default='')     created_by = models.ForeignKey(         settings.AUTH_USER_MODEL,         on_delete=models.CASCADE,         related_name="%(app_label)s_%(class)s_posts",         related_query_name="%(app_label)s_%(class)ss")      # class Meta:     #     abstract = True      def __str__(self):         return f'{self.content} -- {self.created_by}'   class PostImage(models.Model):     image = models.ImageField(upload_to=unique_upload)     post = models.ForeignKey(         Post, related_name='images', on_delete=models.CASCADE)      def __str__(self):         return '{}'.format(self.image.name)  class UserPost(models.Model):     post = models.OneToOneField(         Post, null=True, blank=True, related_name='_uPost', on_delete=models.CASCADE)   class GroupPost(models.Model):     post = models.OneToOneField(         Post, null=True, blank=True, related_name='_gPost', on_delete=models.CASCADE)     group = models.ForeignKey(Group, on_delete=models.CASCADE)  

To do some specific filters ex:

Filter specific group post

Post.objects.filter(_gPost__group=group) 

Filter specific user post

Post.objects.filter(created_by=user) # exclude groups with ```_gPost__isnull=False```  

Create post to user/group

p = Post.objects.create(...) up = UserPost.objects.create(post=p) gp = GroupPost.objects.create(post=p) 

Really I am wondering if this is a sensible approach. The current way of a filter and creating feel odd. So only thing making me hesitant on this approach is just how it looks.

So, is Generic ForeignKey the place to use here or the current multi-table approach. I tried going with inheritance with abstract = True and that was unable to work as I need a foreign key to base post model. Even with no abstract, I got the foreign key reference, but filter became frustrating.