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
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.
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
"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