Maintaining A Secondary, Relative ID Column

Question

Hey, I'm looking for the most efficient way to maintain an object relative auto-incrementing value. In other words, I have a table of objects, and a table of child objects. The child objects need to have a column, which increments relative to the other child objects for the same parent. (ie. child object 3 of parent object 4)

eg.

Parent Objects:
ID|name
1|Object1
2|Object2
3|Object3

Child Objects:
ID|relativeID|parentObjectID|name
1|1|1|Some Child Object
2|1|2|Some Child Object
3|2|1|Some Child Object
4|2|2|Some Child Object
4|3|1|Some Child Object

What's the most efficient way to generate the relativeID column? I'm thinking I should I query for the MAX(relativeID) with the same parentObjectID, then do a the insert, but I'm wondering if that would cause issues if there were concurrent inserts on the same parent object. Is there a better way to approach this?

Answer 1

Add a field to the parent objects table specifying the max number of child sub-id. When you are inserting, make sure to do it in a transaction, so that no one can get in between your reading the max sub-id, and your modifying it.

Answer 2

You have missed stating the Database you are using.

What you could attempt is to retrieve the MAX relative id per parent object and use that in a transaction

This will give you the MAX id per parent object

DECLARE @Table TABLE(
        ID INT,
        ID_RELATIVE INT,
        PARENT_OBJECT_ID INT
)

INSERT INTO @Table SELECT 1,1, 1
INSERT INTO @Table SELECT 2,1, 2
INSERT INTO @Table SELECT 3,2, 1
INSERT INTO @Table SELECT 4,2, 2
INSERT INTO @Table SELECT 4,3, 1

SELECT  MAX(ID_RELATIVE) LAST_ID_RELATIVE,
        PARENT_OBJECT_ID
FROM    @Table
GROUP BY PARENT_OBJECT_ID

Now you can limit this using the parent object id, and only retrieve the MAX id for a given parent objectid. Then you can just add 1 to that MAX found and use that as a new autonumber.

Please let me know if you require some more assistance, or if this does not work in your specific DB.