Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    8

    Question DB schema for multiple composite values?

    Greets,

    We have object model which contains many subclasses and those same subclass types are used in other classes.

    To clarify (simplified):

    class Compositevalue ...
    String value1;
    String value2;


    class Compositevalue2 ...
    String value1;
    SET<Compositevalue> value2;
    SET<Compositevalue2> value3; //recursion

    class ToBePersistedSomeClass ...
    Compositevalue value1;
    Compositevalue2 value2;


    class ToBePersistedSomeClass2 ...
    Compositevalue value1;
    Compositevalue2 value2;
    SET<Compositevalue2> value3;


    Obviously plan one is to make database schema so that Compositevalues are broken to rows into db tables
    representing ToBePersistedSomeClass and ToBePersistedSomeClass2.

    Unfortunately theres lots of nested sets in Compositevalues themselves (containing other compositevalues),
    so plan two is to make relational table for every instance of Compositevalue and Compositevalue2.
    That in other hand leads to hundreds of small subtables and is not very practical to maintain.

    So question is, is there any DB pattern or alike to make only one table for Compositevalue && Compositevalue2
    and still use primary key/foreign key constraints to ensure data integrity?

    Thanks,

  2. #2
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    You can do this using exactly two tables: One for composite values, and the other for nested composite values. Thus, you can build a hierarchy of nested composite values.

    Ravi

  3. #3
    Join Date
    Jun 2004
    Posts
    8
    Quote Originally Posted by rajiravi
    You can do this using exactly two tables: One for composite values, and the other for nested composite values. Thus, you can build a hierarchy of nested composite values.

    Ravi
    Thanks for your reply, but I didn't quite catch it (as Im not pro db designer..yet). Can you 'draw' simple example or alike?

    --qm

  4. #4
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    The two tables would be :

    PHP Code:
      Composite_value (value_idnamevalue,...,
              
    constraint pk_comp_value primary key (value_id) )
    And 
    Composite_Value_Hierarchy (value_idparent_value_id, ...,
         
    constraint fk_comp_value_hier_1 foreign key parent_value_id 
             references composite_value
    (id),
         
    constraint fk_comp_value_hier_2 foreign key (value_id
            
    references composite_value(value_id),
         
    constraint pk_comp_value_hier primary key 
            
    (value_idparent_value_id)) 

    Thus, nested composite value can be handled easily.

    Hope that was clearer.

    Ravi

Posting Permissions

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