Arrays In Sql

Suggest after problem solved, the information rolled into a new SqlTechniques? page so other questions and answers related to Sql can be grouped together

Here's a softball question for the relational advocates here.

I'm doing a project where I need to store an array (actually several, but one will do). The order of elements in the array matters.

What is the recommended best practice for how to accomplish this with standard SQL (I'm using MySql and DBI in perl)?

I've had one suggestion that I build a table with a column for the array_id, a column for the element_index, and a column for the element_value. An array is retrieved with a SELECT on the array_id, and the element_index field is used to order the values.

Is this the best approach? I thought I remembered that "array" had been added to SQL a few years ago, but I can't find a reference.

Why not just use a table instead of an array? Do you want to emulate an array, or store a binary copy of a RAM structure? If not, then it would probably be more natural to just use a table.

I want a persistent form of an array -- it's elements are returned in a specific order, and the order matters. Consumers will be getting and setting values, using the value of the index. Each array is (typically) kept as a member of some other object. -- h00095b006144.ne.client2.attbi.com

A table with two columns should suffice: The element_value and an array_index. The latter is a key; the former is not, unless you want all values to be unique, in which case you have two keys. Basically, that's it; you may want some database constraints to make the array_index contiguous, and one- or zero-based, but none of that is necessary. Inserting and reordering elements might require a little work, depending on details of your DBMS's capabilities and whether the element_values are unique or not. Deleting elements is trivial, unless you insist on contiguity of array_index values.

I'm not familiar with MySql; in particular, I don't know whether it allows you to define constraints or not, nor when it checks them if it supports them. Preferably it would check constraints at transaction end and not earlier -- this makes updates easier.

It would be helpful to see what the requirements are. Generally it is easier to treat data as tables than it is to emulate arrays with tables. For example, posititional arrays are often "sorted in place", whereas sorting a table is usually a view (output) issue rather than something you do to the data itself.