1

I have a column which stores a list of ID.How to implement it?I was thinking of storing it as comma separated values and it's data type as varchar.But I read it is not a good process.I will not be able to perform queries on it without fetching the whole data.I may need queries to be performed on the ID list. What can be a good programming solution to this?

Debangana
  • 87
  • 3
  • give an example and result you wanted – Vijunav Vastivch Aug 02 '16 at 06:45
  • Why do you need only one column and not a separate table (and then join) ? Anyway, you can do a query with comma separated column : `field = 'value' or field like 'value,%' or field like '%,value,%' or field like '%,value'` . Still, it's a bad design and won't make use of indexes. – Mateo Barahona Aug 02 '16 at 06:45
  • Use Association tables or [Junction Tables](http://stackoverflow.com/a/32620163) otherwise it is like an index-less clump – Drew Aug 02 '16 at 06:47
  • Storing a list of id's in one column is very bad database design. A good solution is to read about one to many relationship – Jens Aug 02 '16 at 06:47
  • Your question is not clear to me can you explain it further? – Rupsingh Aug 02 '16 at 06:58

1 Answers1

2

You need a separate table with 2 columns - ID and FOREIGN_ID. You will insert a distinct row for each of your original IDs and will specify the primary key of your original table as value for FOREIGN_ID in the new table. The primary key of this new table would be the combination of its 2 columns. For example, if your original table was 

+-------------+-------+---------+
| Primary_Key | ..... | ID_list |
+-------------+-------+---------+
|      7      | ..... |  3,9,4  |
+-------------+-------+---------+
|      9      |  .... |   5,1   |
+-------------+-------+---------+

your new tables will look like this

+-------------+-------+      +----+------------+
| Primary_Key | ..... |      | ID | Foreign_ID |
+-------------+-------+      +----+------------+
|      7      | ..... |      | 3  |     7      |
+-------------+-------+      +----+------------+
|      9      |  .... |      | 9  |     7      |
+-------------+-------+      +----+------------+
                             | 4  |     7      |
                             +----+------------+
                             | 5  |     9      |
                             +----+------------+
                             | 1  |     9      |
                             +----+------------+
IVO GELOV
  • 13,496
  • 1
  • 17
  • 26