Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
2  
Related: stackoverflow.com/questions/38801/… en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL The exact solution will depend on the availability of the "Common Table Expression" feature in the database –  rwong May 3 '13 at 5:11
1  
Related: en.wikipedia.org/wiki/Nested_set_model –  rwong May 3 '13 at 5:12
    
I'm not sure if I may be misunderstanding the question, but in most linked lists you only need to link in one direction (i.e. you need a ParentId but not a ChildId). There are exceptions to this, and maybe you've hit on one, but in the general case... –  PeteH May 3 '13 at 7:55
1  
If your hierarchies get more complex, try the new data type for SQL Server 2008 - msdn.microsoft.com/en-us/magazine/cc794278.aspx –  JeffO May 3 '13 at 11:26

1 Answer 1

up vote 7 down vote accepted

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.

share|improve this answer
    
+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. –  Emmad Kareem May 3 '13 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 '13 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. –  Bart van Ingen Schenau May 3 '13 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 '13 at 10:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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