How to model many-to-many relationship with self?

From: James A. Hillyerd (jame..illyerd.com)
Date: Thu Dec 09 2004 - 18:12:22 EST

  • Next message: Travis Cripps: "Is it possible to compare entity keys in an object query?"

    Hi, I'm new to Cayenne. I have a question about many-to-many
    relationships, specifically how to relate an object back to it's own type.

    I want to have a model of generic "Node" objects, each which can have
    0-many children (and implicitly 0-many parents).

    Here is a sample database schema definition (for PostgreSQL, but should
    be pretty generic):

    create table node (
         node_id bigint not null,
         name varchar(40) not null,
         primary key (node_id)
    );

    create table node_map (
         parent_node_id bigint not null,
         child_node_id bigint not null
    );

    alter table node_map
         add constraint node_map_parent_fk foreign key (parent_node_id)
         references node (node_id);

    alter table node_map
         add constraint node_map_child_fk foreign key (child_node_id)
         references node (node_id);

    After reverse engineering this schema I played around with the modeler
    some, and was having a lot of trouble "flattening" the relationship.
    Things are really confusing since everything is a Node! It seems like
    things snap into place when I delete the "NodeMap" object entity - did I
    have to do that first?

    I'm attaching my project/map files, maybe someone could take a look at
    them and tell me if I did things right? Also will Cayenne be able to
    keep both the child and parent arrays in sync, or do I need to have just
    a child array?

    One more question... what is the Data View modeler for?

    Thanks!

    -james 

    -- 
James A. Hillyerd <jame..illyerd.com>
Java Software Engineer - ActiveRain.com

    This archive was generated by hypermail 2.0.0 : Thu Dec 09 2004 - 18:12:26 EST