Parent Child relation ship table in SQL Express

I want to build Parent child relation . i have two aproaches .I would like to know which is the best solution ?

1)1st method:-

Parent table with parent id . Child table with child id and parent id.Foreign key relationship exists between parent and child tables with cascade delete option enabled.

Parent TAble

Id name

1 XYZ

Child table

id name parent id

1 abc 1

2 qwe 1

2)2nd method

table with id and parent ID. Top level element will have null value in table. eg

id name parent id

1 xyz

2 abc 1

3 qwe 2

4 adf 1

Retrieve data using recursive queries supported in SQL Express.

Which is the best solution to store parent child relationship?

To know which is better I think I'd have to know what you're doing with the data. Are you developing a deep family tree? A shallow workplace report-to list? How much data? How big are the operations?

Regarding your proposed solutions, your first solution separates people into two tables. That may make it hard to do a single list operation, such as a full roster including parents and children. The second solution leaves you doing recursive SQL to get a hierarchical list of everyone, say if a is parent of b who is parent of c who is parent of d. To get a full list the SQL can be annoying.

There's another possible configuration as well using a join table. You have one table of people, another of relationships. The people table is entirely simple. The relationship table is simply two foreign keys into the people table.

Table USER:

id Name

1 Bill

2 Bob

3 Jim

Table RELATIONSHIP:

parent child

1 2

3 1