Reputation: 1443
I am wondering which method is the best way to store a list of integers in a sql column. .....i.e. "1,2,3,4,6,7"
EDIT: These values represent other IDs in SQL tables. The row would look like
[1] [2]
id, listOfOtherIDs
The choices I have researched so far are:
Which method is the best method to use?
Thanks, Ian
Upvotes: 4
Views: 2057
Reputation: 35470
While Jeroen's answer is valid for "multi-valued" attributes, there are genuine situations where multiple comma-separated values may actually be representing one large value. Things like path data (on a map), integer sequence, list of prime factors and many more could well be stored in a comma-separated varchar. I think it is better to explain what exactly are you storing and how do you need to retrieve and use that value.
EDIT:
Looking at your edit, if by IDs you mean PK of another table, then this sounds like a genuine M-N relation between this table and the one whose IDs you're storing. This stuff should really be stored in a separate gerund, which BTW is a table that would have the PK of each of these tables as FKs, thus linking the related rows of both tables. So Jeroen's answer very well suits your situation.
Upvotes: 4
Reputation: 44438
A single element of a record can only refer to one value; it's a basic database design principle.
You will have to change the database's design: use a single row for each value. You might want to read up on normalization.
As is shown here in the description of the first normal form:
First normal form states that at every row and column intersection in the table there, exists a single value, and never a list of values. For example, you cannot have a field named Price in which you place more than one Price. If you think of each intersection of rows and columns as a cell, each cell can hold only one value.
Upvotes: 8