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.


1 Answer 1


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.

  • +1 for correctly suggesting use of FK since the target is RDBMS. It is important to note that in this case, the Account ID (or ID) is the table PK. Before implementing this design one must determine the expected operations on the data. Say what happens if the user decided to remove "Seattle Mission"? While this structure is valid, I don't consider it trivial to list sub accounts of a given ID. Another issue here is that an entry like "Homeless Shelter" may appear as a sub-account of other accounts (say an account with ID 199). So, normalization should be considered.
    – NoChance
    May 3, 2013 at 9:34
  • Thanks. I am familiar with foreign keys as they relate to records in other tables, but I have never seen a situation where you have a primary key in a table also being a foreign key for another record in the same table. Is this an unusual situation, or is it usually accepted in database design?
    – Ryan
    May 3, 2013 at 17:44
  • @Ryan: I don't know if it is universally accepted, but it was taught in the one class I ever took on Database Design. The example used there was of an Employee table where some employees are the manager of other employees. So it seems like a straightforward solution to a fairly common problem. May 3, 2013 at 17:57
  • +1 This is a fairly well established (and effective) method for storing data in a flexible and scalable hierarchy.
    – Matt
    May 5, 2013 at 10:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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