Autoincrement column for items with hierarchical relationship

We are building an application with MS SQL DB. The application needs to store the hierarchical data in the DB. By hierarchy, I mean the parent-child relationships: each item has only one parent, and may have multiple child items.

The items' table was designed in the following way: 1. itemID column was designated as a primary key (integer) 2. parentID column contains the itemID (the primary key) of the parent item. I has NULL value, if and only if there is no parent for the item.

In the client application, there no natural way to generate the unique IDs for the newly created items. Since multiple client applications may be creating new items and saving them to the DB, the generation of the unique IDs inside the client may lead to synchronization problems.

The natural DB solution for this need is to use the autoincrement column for the IDs. The only issue I see is the need to update the parentId column.

If, for example, items B and C are children of item A, we need to INSERT all the 3 items, and then obtain the autogenerated ID of A, and UPDATE B and C by setting the parentID column to the real ID of A. Since the hierarchy may be complex, I am concerned about performance of that operation.

Anybody can suggest a better solution? We can consider to change the DB design at this point.

Thanks for any hint,

Max Received on Wed Dec 01 2004 - 17:30:55 CET