Should we store comma – separated list in a database?
My suggestion and answer is: Never store comma – separated or delimited list in a Database.
I have found this discussion in many forums and blogs so in this post, I would like to share my opinions and views about 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 very difficult to search any data or value from the list and sometimes we have to use regular expressions to search string.
- We cannot perform any DML operation on a single value from the list without fetching the whole list.
- It is very difficult to get all the records in sorted order.
- It is very difficult to count elements from the list.
- 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 very difficult.
The simplest solution of this problem, is to create separate table and store all the list value into separate tables. You can pass a table parameter in stored procedure and stored all the list value accordingly.