SQL And Arrays?

[kouryuu]

Hi, and greetings.
I'm a little new to SQL so pardon me if I'm asking a question that is stupid.

The situation is that I have a table with a fixed number of fields. Essentially the table represents objects (each object being an individual column.) Each object possesses some inherient properties such as the primary integar key, object's name, the "owner", its "location", and "parent" (previous three are integers referring to another object in the table, think like in an object oriented environment.) But each object/column also has to have available custom properties and values.

I'm curious, is there any way to easily implement Arrays or lists in SQL? I'm trying to think of a way to do this that doesn't involve some delimited BLOB. Since each object may have a different number of properties (depending on which have been added to it, or which have been added to its parents), having a field for each one is impossible unless each object is represented by its own individual table which seems like madness.

[chazzy]

Hi.

Your object concept is off. Essential, each table should be its own type of object (ie i have a table of animals, a table of plants, etc) where each row/set represents a distinct item inside there and has different values for the set of attributes (columns) in the table.

There is a database out there that's "multidimensional" that would work the way you're discussing, but it's slow. The whole point to not using arrays in a database is that its fast and easier to search.

[kouryuu]

Hi.

Your object concept is off. Essential, each table should be its own type of object (ie i have a table of animals, a table of plants, etc) where each row/set represents a distinct item inside there and has different values for the set of attributes (columns) in the table.

There is a database out there that's "multidimensional" that would work the way you're discussing, but it's slow. The whole point to not using arrays in a database is that its fast and easier to search.

Given the fact that the previous version of the software I'm trying to re-write relies on a database consisting of a carriage return delimited ASCII text file in a specific format (it reads the entire file into memory, and then every couple of hours re-writes the file along with whatever changes have been made since then.) the speed I will lose in using an array of some variety will not offset the speed advantages I'm gaining when dealing with any "database" larger than a couple of megabytes, plus it becomes ACID.

No ideas? It appears I can use SQL3 datatypes (which include Arrays) in JDBC 2.0 but if worried about speed I don't think it would help to write it in a language that has to go through a VM (Java).