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!

wait....shouldn't i just have a "parent" field in each record?

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)
);

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:

'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!

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.