Table relationships for family tree or hierarchy

[rubyknight]

Hi all, I've been working with a few ASP+MSSQL applications, now I'm stucked and need help to solve the following problem.The case this time is for a family tree or hierarchy:- Who is at the top of the family tree- Who are the children- Who are the children of the children- Who are the children of the children of the childrenI thought about having a table referencing to itself, like as follow:create table HIERARCHY ( NAME varchar(64) not null primary key, PARENT varchar(64) not null, constraint fk_hierarchy_hierarchy foreign key (PARENT) references HIERARCHY (NAME) on update no action no delete no action)Now, how to SELECT list of off-springs from a particular person--assuming the hierarchy level goes as deep as more than 20 or 100 levels!?!? Can we do this in database level, and not in web-scripting level?

[rubyknight]

By the way, another way to create the table is by having two tables.... first table to contain the list of people... then second table to contain the relationship... like as follow:create table HIERARCHY ( NAME varchar(64) not null primary key)create table RELATIONSHIPS ( OFFSPRING varchar(64) not null primary key, PARENT varchar(64) not null, constraint fk_relationships_hierarchy foreign key (PARENT) references HIERARCHY (NAME) on update cascade on delete cascade)This second method allows update and delete cascade actions, but the problem remains.... who to neatly SELECT who are offsprings of a particular person?The third option is having a hierarchy-level column attached to the table... like as follow:create table HIERARCHY ( NAME varchar(64) not null primary key, LEVEL int not null)Using the above method, we can find out all the people above certain level, but still can not filter based on who's offspring? (remember the hierarchy levels might go beyond hundreds)...By the way... No, I'm not making a family tree of some endangered species or monsters from aeons ago.