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

Answer 1

And why additional table steps(processID JOIN, step INT) is not an option? I'm pretty sure it would be the easiest to maintain/code.

SELECT process.name FROM process, steps WHERE process.id = steps.processID AND steps.step = 3;

Pardon my SQL, but it's been a while :)

EDIT: UNIQUE(processID, step) would be advisable.

Answer 2

I'd use a simple and canonical relational design: CREATE TABLE ranges (process_id int, num_low int, num_hi int). Latter two columns specify range. Independent index on each column. For "special" infinity values just use maxints or additional boolean columns.

Advantages: easy search for whether a particular numer is in range, or whether ranges intersect. Easy maintenance. General understandability and simplicity.

Disadvantages: some logic required when modifying the set, i.e. checking whether newly inserted or modified range intersects. Splicing ranges may be required.

Answer 3

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.

Answer 4

The answer below to do a subtable (MitMaro) is the "standard" way.

IF you MUST put a set of numbers into one column or a table though the only way I can imagine is to use bitwise operations to store the set and you can use bitwise operations in you data queries to look for specific bits being set. Quick google searching indicates MSSql 2005 supports this but only up to 32-bit int, so if you steps pass 32 you will encounter issues.

All in all, the subtable is the most standard it would make for somewhat more understandable queries against the table(s). This is also the safest to support any future case where you would ned larger than 32 value maps.

Answer 5

If you aren't tied to SQL Server, Postgresql has great support for this kind of thing via an array. They even have a special value for infinity.

If you're tied to SQL Server,MitMaro's way is best.

Answer 6

create table setmember (setid int, setmemberid int)
create unique nonclustered index idx_setmember_idx1 on setmember (setid, setmemberid)

Let me assume a magic number (-1 or 999999999) for "all".

This will be highly performant for both querying on a per set basis, and update insertion via the nonclustered index. Uniqueness enforces no repetition of entries. It is problematic to enforce either "all" or else multiple set members as a constraint, but there are diminishing returns though it could be done in a trigger.

Additionally add

create nonclustered index idx_setmember_idx2 on setmember (setmemberid, setid)

to allow for efficient reverse lookup queries.

If you use array types, you may be unable to implement efficient reverse lookup.

Note all the SQL above is ANSI compliant.

Answer 7

Ended up using a solution to a similar question.

Thanks anyway, though! I enjoy reading everyone's opinions on these esoteric areas of database design.