I am working on an application that connects to another application (Quickbooks)
ID: 643885 • Letter: I
Question
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.
Explanation / 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.