2

I am trying to implement tag system similar to one which StackOverflow has. Obviously I've read multiple articles including this answer.

However my scenario is little bit different

  • there will be limited amount of tags which can be only created by user with higher privilege (anybody can assign a tag there). This excludes option #1 (from SO question I linked above, each tag is inserted directly into the tables tags column and then it's queried with LIKE) I guess
  • there are also multiple tables in DB which can be tagged (currently five)

Especially second criteria makes it harder so these are my thoughts

  • I could follow option #3, have table tags and have M:N relationship with each table. However that would make searching harder (imagine that join if the table number grows) and also I need to tell which table (application module) matches the tag in a search result
  • I could use some kind of polymorphism but I am pretty new to this concept regarding to the databases so is this something which fits to this problem well?

I use newest version of PostgreSQL.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Petr Mensik
  • 26,874
  • 17
  • 90
  • 115

1 Answers1

1

Since you are using PostgreSQL, you have the option of some field types which aren't available for other databases. Particularly, arrays and JSON fields. I did some performance comparisons of the various methods in a blog post. Arrays and JSONB were definitely better options than a tags table for any search which needed to combine multiple tags.

Given that, I would recommend creating a tags column for each table on which you want to have tags, either an array or a JSONB column, depending. If you need to search over multiple tables, I'd suggest a UNION query instead of having a single monolithic tags table which joins to everything.

FuzzyChef
  • 3,879
  • 2
  • 17
  • 17
  • I thought about that but wouldn't UNION impact performance a lot while doing it over couple of tables? – Petr Mensik Jun 14 '17 at 08:00
  • Only if you're going to join the result against something else. Since you're collating 5 tables, you'll have to UNION *something*. – FuzzyChef Jun 14 '17 at 21:32