In sql, what is the best way to store several ordered vectors/lists?

Question

I have a few integer lists of different length of which I want to preserve the order. I will be using them as alternative to each other, never 2 at the same time. The number of lists might grow in the future, although I expect it never to reach a value like 50 or so. I might want to insert one value within this list. These lists are relatively seldom modified, and using a manual editor like MS SQL Server Management Studio for this purpose is fine. For what I can see in this moment, these lists will be rarely used to directly make queries, there will be some C# in between.

For storing one ordered list, a linked (or double-linked) list seems appropriate. But if I have to store several ordered lists, it seems to me that I will have to add one table for each one of them. The same is valid if I use an indexed list. On the other hand, I could also store all these lists in one table transforming them in strings (one string per list) with values comma separated, that I would then parse in my C# program.

In sql, what is the best way to store several ordered vectors/lists in sql?

Answer 1

Several SQL implementations now have (years after this question was originally asked) a "vector" type.

• SQLite now has sqlite-vec : a vector search SQLite extension

• MariaDB now has MariaDB Vector

• PostgreSQL now has pgvector

• MySQL now has the VECTOR type; (see also How do I create a field with a vector type in MySQL? and Storing vector coordinates in MySQL )

• Azure SQL Database has the Vector data type

They have some advantages and disadvantages compared to the traditional "bridging table" approach in Nickthename's answer.

Answer 2

A kind of this?

ListId   ItemOrder ItemValue
1        1         10
1        4         7
1        2         5
2        1         55
1        7         23
2        4         15

Select ItemValue FROM [Table] WHERE ListId = 1 Order By ItemOrder

Here Each list has an ID (you can use a clustered index here) and the order is given by the field ItemOrder

Answer 3

Relational databases like SQL Server typically don't have "arrays" or "lists" - if you need to store more than one value - they have tables for that. And no - it's in no way cumbersome for a database like SQL Server to have even thousands of tables - if you really must..... After all - handling tables is the core competency of a relational database ... it should be good at it! (and SQL Server is - and so are many other mature RDBMS systems, too).

And I would strongly recommend not to use comma-separated strings - those first of all violate even the first normal form of database design, and as soon as you do need to join those values against something else - you're in trouble. Don't do this - there's absolutely no need for this, and you'll just make your developer life miserable sometime in the future - if you have the opportunity to avoid it - don't do it!