www.webdeveloper.com
Results 1 to 3 of 3

Thread: SQL And Arrays?

  1. #1
    Join Date
    Jan 2006
    Posts
    2

    SQL And Arrays?

    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.

  2. #2
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    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.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  3. #3
    Join Date
    Jan 2006
    Posts
    2
    Quote Originally Posted by 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.
    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).

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles