Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Suppose that I have the following logical information: (tag_name, id). For example: (food, 1),(food, 2),(food, 3),(drink, 1),(drink, 2)

I'm trying to decide what is the best way to store it in a database. I can simply use a (STRING, INTEGER) table like in the example, or a more compact (STRING, INTEGER_LIST), like so: (food, [1,2,3]),(drink, [1,2]). This method is definitely more compact, but the other method seems more trivially flexible (for example, I can do a join by tag_name or id with another table, and I don't need to parse the results of my SQL queries).

Which method is better, and why? Is the redundancy of the first method overwhelmingly wasteful, or are SQL databases smart enough to figure out efficient storage for that? Is the post-query processing that the second method needs (like checking if an id is already associated with a tag before adding it) more expensive than putting this burden on the database in the first method?

share|improve this question
What is the redundancy in the first method? – user2313838 Jul 4 '13 at 14:53
up vote 6 down vote accepted

Never, ever try to stuff set-typed values into one attribute because it looks easier than creating another relation. Data bases live for handling relational data efficiently. Unless you think you could write a better storage method than e.g. the Postgres or Oracle maintainers, it is a bad idea to try to manage relations by serializing and processing them yourself.

share|improve this answer
THIS! For the lifetime of the database schema (believe me, that will be plenty longer than you care to imagine right now), any time you are dealing with more than one value, you are going to be beating your head against a wall trying to perform even simple operations. As one example, what could be a simple JOIN might have to be done with a subquery involving substring operations. Make a guess as to what that will do to your application's performance. Hint: it'll be a killer, and not in the sense of "killer application". – Michael Kjörling Jul 4 '13 at 14:14

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.