1

or better, is this good practice?

I would like to store bookmarks, each bookmark a row. I want a tag column that is an array in string format that hold the hierarchy of tags that define it. For example:

url | tag
'stackoverflow.com', '[faves, internet, QandA]'

My current table is below, and I just want to modify the tag column to hold the array in string form.

enter code here

stack overflow
  • 187
  • 10
  • No, it's not a good practice at all. By storing your tags in a string you will lose certain abilities such as using the tags in a conditional. Consider creating a new table for tags. – Derek 朕會功夫 Oct 01 '17 at 22:22

1 Answers1

1

No, it's not usually a good idea to store a comma-separated list in a string.

Exception: if your queries always treat the whole string as a string, and you have no need to use SQL expressions to search for individual elements, then there's no harm in doing this.

In SQL, it's better to treat each column as a single scalar value, not an array. Working with data gets awkward and inefficient if you treat a column as something that can be broken down into sub-parts.

For your tags, you should have a one-to-many tag table so that each bookmark can be referenced by many tags, one per row in the tag table.

Or really, a lookup table of tags, then a many-to-many table mapping bookmarks to their tags.

CREATE TABLE Tag (
  tag_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tag VARCHAR(50) NOT NULL
);

CREATE TABLE BookmarkTag (
  bookmark_id INT NOT NULL,
  tag_id      INT NOT NULL,
  PRIMARY KEY (bookmark_id, tag_id),
  FOREIGN KEY (bookmark_id) REFERENCES Bookmark(bookmark_id),
  FOREIGN KEY (tag_id) REFERENCES Tag(tag_id)
);

This gives you a lot more flexibility, for example:

  • No limit to the number of tags per bookmark
  • Easy to search for a specific tag
  • Easy to count how many tags per bookmark
  • Easy to prevent duplicates, control the tags allowed, delete a given tag from every bookmark that uses it, etc.

See also my answer to Is storing a delimited list in a database column really that bad?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • If you have answered this question already in another question, then you should vote to close the question as a duplicate, you should not answer it again. – Shadow Oct 01 '17 at 23:13
  • @Shadow, questions are not identical, even if the answer is the same. If I were to close the question as a duplicate, I'm likely to get complaints from other users saying that I closed it inappropriately. – Bill Karwin Oct 01 '17 at 23:23
  • They are. Both ask if it is a goid idea to store data in a delimited format within a single field. – Shadow Oct 01 '17 at 23:49
  • It's a judgement call. I'm well aware of how to use Stack Overflow. – Bill Karwin Oct 01 '17 at 23:55
  • I'm sorry. What is the suggestion for the table structure? – stack overflow Oct 02 '17 at 05:58
  • @stackoverflow I updated my answer with an example. But for what it's worth, this has been answered years ago too: https://stackoverflow.com/questions/20856/recommended-sql-database-design-for-tags-or-tagging – Bill Karwin Oct 02 '17 at 16:29