Various Way To Store hierarchical Data

Yes, a database is the best way to store this type of hierarchical data. In fact, you can store an infinite amoutn of levels using just a single table.

You simply have the MenuItem table reference itself.

MenuItem
-----------------
id   int
itemName    varchar
parent     int

That parent column can be null (top-level category) or it can be a number referencing another item in the table, which would make it a sub-category of that item. If it doesn't have any children, it is expected to be a leaf or end-node instead of a branch.

And as @BrandonGalderisi stated, SQL Server would be a good db to use.

>In fact, you can store an infinite amoutn of levels using just a single table.

That's an understatement.  You can store an infinite (well, a lot, anyway) amount of levels of an infinite amount of hierarchies using just a single table.  Not that I would recommend it.

While storing hierarchies in SQL databases is a breeze, retrieval is not always so easy.  Google the topic to find three or four techniques, each lending itself to different retrieval needs.

Yes, @dqmq is exactly right. Retrieval is a little bit more time-consuming. It is best to build a view that captures your physical levels so it can return your data quicker.

Building a view won't help.  It's all in how you structure it and that's dependent upon how you intend to use it.

You can use the HIERARCHYID data type in SQL Server 2008.  If you are still using SQL Server 2000 or 2005 use Xml for this purpose.

How would I go about using XML in 2005? I believe that the retrieval process is the most difficult part that I'm facing right now. I need to get the data back into the XML format that I currently have in my flat file so that my SKM menu .NET control can display it.

>>I need to get the data back into the XML format that I currently have in my flat file so that my SKM menu .NET control can display it.<<
Then save the entire menu in one Xml column/row.

Won't there be contention with multiple applications writing to a large XML field.  

I really like the CTE solution but the question that comes to mind now is, how do I manage sorting between all the menu items? Do I have to use table locking to keep sorting consistent between simultaneous user insertions and updates? I can see CTE working for the retrieval of the menu but inserting a rank or priority value and keeping the integrity of that value is hard for me to picture.

For instance, if I BrandonGalderisi moves a menu item from the first position to the third position while I am inserting a menu item at the first position, where does Brandon's menu item actually land?

Well the problem is simpler than that while at the same time being more difficult.

There is no such thing as "two people writing at the same time" in SQL Server.  I'll follow up to that.  You both cannot write the SAME record at the SAME time.  So you could insert a record to position 1 at the same time someone else is updating a record to position 1.  Who wins?  Whoever updates last.  But what really needs to happen is that when you are updating you should account that the data you had may not be the most current.  So if you say "move item 1 from position 3 to position 2" and item 1 is no longer in position 3, then your data is outdated and you should refresh it before attempting to make any changes.

Yes, you need to locks to prevent concurrent users from stepping on each other's toes.  While it is true that two users won't update the same record at the same time, life is not that simple when you're reordering multiple records and/or you don't want concurrent access to the records while the update is "in the works".

Locks do cause contention, but it's "good" contention because it protects your the integrity of your data.  With page level or row level locking, the inconvenience will be nowhere near what you experienced when locking the entire hierarchy at once or by putting the entire hierarchy XML.

Check out Optimistic and Pessimistic locking strategies on Google for two different approaches.  With optimistic, users are allowed to proceed through an update sequence only to be warned at the end that they have created a conflict.  It's a frustrating user interface, but the advantage is that locks are short term so conflicts may not occur that often.

With pessimistic, users are prevented from changing records that others are working on.  Much more friendly, but requires locks to be held for much longer periods of time.

Also, check out the BOL discussion about transactions.  You will want to use transactions to tie together updates that involve multiple records.  

Also, check out the BOL discussion on transaction isolation level.  Various options give you control over the degree of locking.





 



 

That is why writing the whole Xml to one record is always going to be a better approach (there is no locking involved).  Unfortunately, until you take the time to test it out, you will not see it for yourself.

Good luck.

>That is why writing the whole Xml to one record is always going to be a better approach (there is no locking involved).

I beg to disagree.  Without a locking strategy, you will have a massive epidemic of lost updates and obsolete reads.  With a locking strategy, no two users will be allowed to work concurrently because you have to lock the the hierarchy instead of just the records being changed.



 

>>I have a website where we are currently using an XML file to store our menu content.<<
Your solution is quite simple:

1. Add an Xml column to your table.
2. Add the the contents of that file to the column.
3. Change where you are writing out the Xml to a file to instead update the single (this is the important part) value in the table.  Again no locking involved or needed.  The value will be updated in its entirety.

Test it out and you will see this is a vast improvement over handling it as a file without the need for a complicated hierarchical structure.  Above all else, you will be using something that you are familiar with.

Again, good luck.

>Your solution is quite simple:

If AC is correct, your solution is even simpler:

1. Change where you are writing out the xml to a file to instead update the single (this is the important part) value in the file.

In other words, with that solution you gain nothing relevant to the discussion by moving the hierarchy to a database.

>Again no locking involved or needed.

I cannot let that statement pass.  Even in the simple, one-record scheme, SQL Server uses locks to prevent data corruption.  It's a complex topic and I want to avoid a 20-page tutorial about it, but it's way misleading to claim there is NO locking involved or needed.  Locks may be implicit and short-lived, but they are used none-the-less.