enter image description here

If I want to find all the children of "John", i need to run SELECT * FROM Child where Father_Name = "John" which searches through the entire Child table.

Is there a way to design it such that the Father table will contain the list of children so that i can get all children of "John" by simply searching for the "John" row in the Father table?

  • 4
    Why do you think it searches the entire Child table? It will use an index to locate the children of john an the n acces these rows. But not the entire table. – miracle173 Jun 29 '17 at 4:19
  • Would it still take log(size of Child table) to locate the children? – James Jun 29 '17 at 4:35
  • 2
    If an index i used, 2 or 3 datablocks from these index will be needed. Maybe the first (and even the second) are already in the cache. then only 0 to 2 datablocks must be read from the disks. There are RDBMs',e.g. Oracle, but maybe not MySql, that can be advised to store related rows of two tables in the same datablock to minimize disk access for such joins. – miracle173 Jun 29 '17 at 5:22

Your current design is the way relational is supposed to work. Stick with it.

What you are proposing is denormalisation. It can solve some problems but can cause a whole bunch more.

If you really want to do it you'll have to decide how to hold a variable sized, perhaps empty, array of Child values. JSON or XML are likely to be the least bad options.


You do need INDEX(father_name) on that table for efficient use, and to avoid a table scan. Creation of such an INDEX is not automatic.


Yes you can store the relation in one table. Then you can self join(inner or left) depending upon the requirement.

declare @Father table(id int,name,FatherID int)
insert into @Father values(1,'John',null),(2,'Peter',1)

Let me know what else is your requirement related to it.

Sorry now I understood your question . I was thinking it as Employee- Manager Relation.

So from Employee- Manager Relation point of view my design is correct.If same Father can be child of another person.

In fact now you have to give some more example.

You are thinking in this manner

declare @Father table(id int identity(1,1),FatherName varchar(50),ChildName varchar(50))
insert into @Father values ('John','pqr')

Can you populate record as per your requirement ?

  • I think my above answer is correct .Isn't Employee and its manager store in same table ? Everyone can understand the design by script above.Also I wanted to know what is the side effect of it than putting FatherID in diff table. – KumarHarsh Jun 30 '17 at 4:45
  • This doesn't solve the concern. Still log(n) scan on the table to find all children of a father, given a father row. – James Jul 7 '17 at 2:03
  • you haven't read my answer completly.I asked you to throw more example of Father -Child and Father whose is also child of someone.Then only I can reply about "Still log(n) scan on the table" – KumarHarsh Jul 12 '17 at 3:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.