This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Should we store comma – separated list in a database?
My suggestion and answer are: Never store comma – separated or delimited list in a Database.
I found this discussion in many forums and blogs so, in this post, I would like to share my opinions and views on this topic.
Why we should not store comma – separated or delimited list?
We cannot enforce uniqueness inside the list, e.g. list can be like this: 5, 4, 8, 8, 8, 6.
We cannot ensure right data-type for all the values, e.g. list can be like this: 5, 8, 7, temp, abc, $, 8.
Do not possible to check referential integrity.
It is tough to search any data or value from the list and sometimes we have to use regular expressions to search the string.
We cannot perform any DML operation on a single value from the list without fetching the whole list.
Getting all the records in sorted order is tough.
Counting elements from the list is terrible.
When we are storing integer data into the list, it takes twice the space than binary integers.
We cannot use list value in the join conditions.
When we store the comma separated value, the overall maintenance of this list and data is complicated.
The simplest solution of this problem, is to create a separate table, or you can use data type like XML, JSON.
