Database Design – Representing Parent/Child Hierarchy in Relational Databases

cdatabase-design

I am working on an application that connects to another application (Quickbooks) using an API by sending/receiving XML requests/responses. I am able to work with the data in the XML response and load it into a datatable in c#, so that's not the issue. What I am looking to do is write the data from the Quickbooks XML file to a relational database such as SQL Server so I can make it available to a web app or other applications.

The problem I am having is that for the Accounts object, there are multiple child entities (up to 5) for a given parent. For example:

  • Parent: Revenues
  • Child: Charitable Donations
  • Sub-Child: Homeless Shelter
  • Sub-Sub-Child: Seattle Mission
  • Sub-Sub-Sub-Child: Food

This example is a bit extreme, as the particular file I'm working with only goes out to three levels, but a maximum of five is allowed by Quickbooks (thus I'm trying to come up with a solution that would scale well). When I create my datatable in C# from the data in the XML file, I have two columns: ParentID, ChildID. It would look like this:

ParentID     ChildID     Account               Balance
--------     -------     -------               --------
001          002         Revenues              45670
002          003         Charitable Donations   1500
003          004         Homeless Shelter        800
004          005         Seattle Mission         400
005          NULL        Food                    200

In this example, I am able to determine all of the child IDs of a parent, and then look to the ParentID with that ChildID and see if that child has any children, etc. I have a method which does a fairly good job of parsing through that, and it works fine for the time being, but is this really the best way to store it in a database if I wanted to permanently house the data in a relational database? It seems like there should be a more elegant solution, but I'm not sure what that would be.

Best Answer

If I understand you correctly, you have an Account entity where one Account can link to one or more other Accounts.

The canonical solution to store such a relation in a relational database is to give each row of the Accounts table a foreign key that links back to the parent entry in the table.

In your example, this would look like this:

ID  Parent  Account name          Balance
--- ------  ------------          -------
1   NULL    Revenues              45670
2   1       Charitable Donations   1500
3   2       Homeless Shelter        800
4   3       Seattle Mission         400
5   4       Food                    200
6   4       Housing                 100

I have added an extra row to show how this scheme allows a parent to have multiple child entries. By filtering on the right columns, you can retrieve both the parent and the children of each entry.

Related Topic