0

Recently I read an article about building a tag table for better seraching post by tags. The author suggested using one-to-many relationship with post table -> tag table with tag as text[] type. the performance were really good, especially pagination.

he showed how a many-to-many normalized post table - tag join table - tag table tag as text type - is way less performant.

At this point it made me question, if thats the case, then when do I have to make those normalized tables with many-to-many? as textbooks often tells us to normalize tables.

In my backend, besides tags, I'll employ a global category table, which will have a set 20 predefined category name that each post must make at least one relation to.

from his experimentations, it makes me want to build category table his way as well of one-to-many using text[] ... but I'm unsure it is wise to go what the logical model of many-to-many.

I'm a new learner of building databases with rdbms.

article:

http://www.databasesoup.com/2015/01/tag-all-things-part-3.html

2
  • The post you link to is not so much about (de)normalisation as it is about using the GIN index.
    – mustaccio
    Nov 27 '21 at 13:33
  • I see, i did a little more digging, i suppose database designers should start building a normalized database, and only denormalized when there's a need for it such as the example here? going from many-to-many with regular index to one-to-many with gin index?
    – cozycoder
    Nov 27 '21 at 16:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.