design question - parent child tables & identity columns

I don't know if this is the right forum but...

In a parent/child table structure (order/orderdetail) I have used identity columns for the orderdetail or compund primary keys. I find a single identity column on the detail table easier to manage (with a fk to the parent) but what ends up bieng easiest for the user is to have an order (say #3456) and detail items listed sequentially from 1 to n. This reflects a compound key structure but generating the 2nd field is a pain. Is there any way to tie an identity field to the parent key so that it will generate this number for me automatically?

Nope. But, that also brings up a very interesting point. Why is your user directly querying the data within the table in such a way that they would even see this value? It's an internal structural element and should not have any business meaning at all. Put an identity on the orderdetail table, link it to it's parent OrderID in the orders table. Now, in the application which your users should be using to work with data, yank the order detail rows out, display them in ascending order of the OrderDetailID, and then on the application tier just number the lines from 1 - N. The user now gets the display that they want and you aren't tying a structural element to a business meaning.

Why wouldn't you want to give this key a business meaning? Very simple. User enters an order with 10 line items. They then decide to delete lines items 3, 6, and 7. If you are physically storing this data, you would then have to update the foreign keys and then the primary key (parent) upon which they depend which is something that should never be done.

|||

The users are not directly querying the data. The might see it in a report. Users see keys all the time and they are very useful. An invoice number is a key, a UPS tracking number is a key, a Bloomberg trouble ticket is a key. They are very useful in tracking down problems. When these objects have detail items users do communicate using the codes ("I have a problem with order #2356, item #5"). I was trying to find a way to make this easy by keeping the 2nd number low ("I have a problem with order #2356, item #58694937").

I wouldn't need to delete the rows, I could mark them as deleted and strike through them on a report or just eliminate them from the report and have gaps in the numbering.

|||So the answer to that is you have to custom code that yourself which is also going to mean performance, scalability, and concurrency issues.