3 Replies Latest reply on Nov 23, 2001 3:35 PM by Andreas Schaefer

    Array in Database

    Sergio Bello Newbie

      I'm working with Entity Beans. I want to have an Entity Bean with an array as a persistent field. I need an array with references to other Entity Beans, because I`m working with tree structures, and one element can have many sons (I'm implementing each of this elements using an Entity Bean). I want to know who can I do, because I haven't found any mapping to Java class array in standardjaws.xml. Then, I will have to do find operations based in elements of that array, so I can´t use mapping to Object.

      Thanks a lot

      Cunhao

        • 1. Re: Array in Database
          Andreas Schaefer Apprentice

          I think your DB design is not good. To implement a tree structure on a DB (where each element is of the same table) the table contains a foreign key pointing to the primary key of the same table:
          CREATE TABLE my_tree (
          ID NUMBER NOT NULL,
          NAME VARCHAR( 100 ) NOT NULL,
          PARENT_ID NUMBER NULL
          );

          The PARENT_ID is the foreign key and contains the value of an ID of its parent record. Note that I allowed the PARENT_ID to be NULL because the root does not have a parent and therefore I did not create a CONSTRAINT.

          I don't know any relational DB which allows an array as attribute type because it would go agains the 1. normalization (atomizity of an attribute).

          Have fun - Andy

          • 2. Re: Array in Database
            David Jencks Master

            I think cmp 2 may be the easiest way to do this.

            Firebird/Interbase has arrays as a data type (not exposed through the interclient or my jca-jdbc driver however). It's also part of the jdbc standard.

            IMO the use stated is a violation of relational db design principles, but the use of arrays not containing foreign keys is not a violation. To me, the "atomic" means you don't want to do relational operations on the values of the column. For instance, dates and timestamps are often considered atomic, but there are plenty of extract year from xxx functions to play with.

            • 3. Re: Array in Database
              Andreas Schaefer Apprentice

              You can always use your own schema on top of an attribute and the DB won't complain but you have to deal with it. Therefore when you create a BLOB and add Java objects in it then you have to recreate the Java object afterwards. You example of the date is especially tricky because
              1) date field is part of the SQL-92 standard but the
              implementation is up to the vendor and this is
              different on each DB
              2) How you extract info from the DATE is also up to the
              client.

              "Atomic" means that each attribute does not have a sub-structure. When you would create an Array with a static number of components maybe this will work but what is the difference then between:
              - test ARRAY[ 10 ]
              - test1, test2, etc.

              Have fun - Andy