Matt-

Using a surrogate key doesn't have to change any of what you're doing. You can still have a compound index on po_num+item_no and use that to control the display order.

I assume your item numbering scheme is like a sort ordering number? So that you have the primary (surrogate) key in your item table, but the item_no that the user sees is simply a number that the display is listed by (for example).

Now what do you do when they have items 1 through 30 (straight through) and they want to "insert" an item between 25 and 26?

p.s. Don't worry, I'm sure will Jim will answer, too. < s >

>I have always used an numeric field in the child table with a compound index of "po_num+item_no" and automatically assigned the "item_no" value in 10 increments (like old BASIC line numbering). This way, the user can add line-item 25 between line-item 20 and 30. If you auto-assign the "item_no" value in increments of 1, there is no simple "insert between" scheme.
>