Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2001
    Posts
    3

    1-M parent-child relationship

    I have a parent-child situation where the parent can have multiple (n) children. And of course, a child can be a parent of multiple children.

    How is this best handled?

    I was thinking of a table (basically a bridge entity) where a parent can be associated with 1 or more children. The parent and child PKs (from the original table) would be combined to form the PK in this new table. Would that work…or is there a better solution?

    Thanks!

  2. #2
    Join Date
    Sep 2001
    Posts
    3
    wait....shouldn't i just have a "parent" field in each record?

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Are you talking about a recursive relationship? i.e. the parent and child are the same table? I think you are, in which case your second post is correct. For example:

    create table employee
    ( emp_id integer primary key
    , emp_name varchar(50) not null
    , manager_emp_id references employee
    );

    However, if the relationships can change over time and you want to track that then your first post would be more applicable:

    create table employee
    ( emp_id integer primary key
    , emp_name varchar(50) not null
    );

    create table employee_manager
    ( emp_id references employee not null
    , manager_emp_id references employee not null
    , start_date date not null
    , end_date date
    , primary key (emp_id, start_date)
    );

  4. #4
    Join Date
    Sep 2001
    Posts
    3
    Thanks for the reply.

    Yes, I'm talking about a recursive relationship. The relationships won't change (meaning, once a record is deemed to be the parent of another record, that will never change).

    If it's not asking too much, could you show me an example of a query to retreive data recursively.

    For example:

    Code:
    INSERT INTO EMPLOYEE VALUES (1, 'SOME_MANAGER', NULL);
    INSERT INTO EMPLOYEE VALUES (2, 'EMP_A', 1);
    INSERT INTO EMPLOYEE VALUES (3, 'EMP_B', 1);
    INSERT INTO EMPLOYEE VALUES (4, 'EMP_C', 3);
    'SOME_MANAGER' (EMP_ID 1) is the direct manager of 'EMP_A' and 'EMP_B'. 'EMP_B' is the manager of 'EMP_C'.

    How would I query to see EMP_C's boss, and his/her boss, and his/her boss, etc?

    Thanks for you help!

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    There is no standard SQL way to write such a query: the way to do it depends on your DBMS. For xample, in Oracle there is a special CONNECT BY clause:

    select emp_name
    from employee
    connect by prior manager_emp_id = emp_id
    start with emp_id = 4;

    I don't know how to do it in other DBMSs - apart from via procedural code.

Posting Permissions

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