How do I best store a list of numbers in a relational database?

Question

I want to store a list of numbers (essentially, a set in mathematical terms) in a relational database, specifically SQL Server 2005.

Ideally, I'd like it to be a single column on a given table, but I'm willing to hear any sort of solution. The data I need to store is, like I said, a set of numbers.

• It isn't required to be sequential (i.e. gaps are okay, normal, and typical)
• Ranges are possible (i. e. 1 - 4) but while I'd like to display it that way I'm fine with using shortcuts and such for storing it
• It can also be "all", so at least one value must be reserved, preferably logically, for this "infinite" case
• The list of numbers need not be in order (i.e. 3, 2, 9, 5) but it is preferable and perfectly reasonable that they will and can be sorted prior to inserting as only code will be doing the inserting, not manual users. Still, it should probably not rely on or expect the list to be already sorted.
• The set of numbers should be easily searchable for a subset (see below)
• All numbers should be distinct (no dupes), but this can and will be enforced before insertion

This column is meant to store all of the "step numbers" of a given process that the row applies to. Each row can, therefor, apply to one or more steps, in any order, range, or sequence. The maximum number of steps possible (the max range, essentially) is different from row to row, though I highly doubt any of them will get into the hundreds, so in 99.9% of cases the maximum should never exceed 20 or 30, and I'd be surprised if it ever got anywhere close to 100. Each row is guaranteed to have one value (step) at minimum (i.e. it doesn't make sense to have a row that doesn't apply to any step), but I figure this is as simple as setting the column to not null.

However it is stored, I'd like it to be easily searched. For instance, I'd rather not have to jump through a lot of hoops to write an SQL query to find all rows that apply to "step 3" for instance. If a given row has several steps it applies to (say, 2, 3, 7, and 8), it shouldn't be too difficult to match it when searching by step 3.

Also, while I'd like it to make some sort of logical sense when looking at the raw data (for anyone that need work on the system after I'm not around to ask and so they don't have to read thick documentation to figure out my obscure encoding), I'm willing to compromise on this. Encoding the list into something that can be reliably decoded is, thus, acceptable.

I apologize if this is a dupe — I've been googling around but I suspect this issue of mine suffers from not knowing what to search for or how to phrase or call it to find what I'm looking for.

On a more commentary note, I wonder if this isn't one of those areas where relational databases fall short. Unfortunately, I don't have a choice here. I must store it in SQL Server. Saving separately to a file or some other persistent data storage is out of the question, I'm afraid.

Answer 1

I can't remember the correct terminology for this but the correct way to do this would be to create a table like the one below:

|  id  |  table1_id  |  value  |
--------------------------------
|   0  |          1  |      1  |
|   1  |          1  |      2  |
|   2  |          1  |      3  |
|   3  |          1  |      7  |
|   4  |          1  |      9  |
|   5  |          2  |      1  |
|   6  |          2  |      3  |
| ...  |        ...  |    ...  |

For each value in table1 you add the required values into this table.

For 'all' you can create a column in table1 which is a flag you can set if you want all. (I use 'enum' in MySql but I am not sure if this exists in SQL Server).

I am not sure if there is some Sql Server specific way of doing this since I use mostly MySql.