I am working on an application that connects to another application (Quickbooks)

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.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.