Hoping some SQL guru can help me with this question.

I'm creating a new web app to help manage our projects. I want a hierarchy like:

Project - WorkOrder ----Task

the Project.ID will be an int with identity starting at 1000

I want the WorkOrder.ID to increment by one but within the scope of the project. For instance the first work order for project 1001 would be 1, the second 2. The task.id would increment by one within the workorder scope.

1001 (project) - 1 (work order) ---- 1 (task) ---- 2 (task) - 2 (work order) ---- 1 (task) ---- 2 (task) ---- 3 (task)

1099 (project) - 1 (work order) ---- 1 (task) - 2 (work order) ---- 2 (task)

Is it possible to handle this workorder.id and task.id sequencing in SQL Server?

Thanks for any help.

You can't use identity columns for that, so you have to do your own sequence logic here. One thing that's then always pops up is the matter of 'gaps'. The thing is that no matter what you do, you will have gaps inside a sequence sooner or later, OR your code will likely be unreliable. This is caused by the fact that you have to determine during a transaction what the sequence number will be, you then insert that row and if the transaction rolls back, the sequence number you used is effectively not used at all.

This then leads to a 'gap' in the sequence, if you for example have 2 transactions in parallel doing the same inserts (so one inserts id '1' and the other one inserts id '2'. The '1' fails, and the '2' succeeds for example).

This is unavoidable, as you can only solve it by allowing only sequential access to the tables during saves and that's for a complete transaction, which of course is not performing well due to that.

yea I can surely see that happening at some point. My goal with the numbering is to make it human readable.

1001-1-1 printed on a folder or note is immediately understood as the first task for the first work order on project #1001.

i was hoping SQL Server in particular would allow for this within the identity settings.

Do you think I could use a custom type? I only know about those but I can probably figure it out if it would work.

I would use the built in identity functionality for the true primary key and use a custome function (either in sql server or in your application) to produce and keep accurate your human readable keys.