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.