Auto increment multiple column values from parent id

Question

I have 2 Parent tables like DEPARTMENT and COUNTRY. In both of these tables, I will have a column saying LAST_GENERATED_SEQUENCE. Basically, this will allow me to maintain order of child record creation (order can be re-arranged manually later though)

Now when I insert a record in the child table EMPLOYEE, It will insert DEPARTMENT_SEQUENCE, and COUNTRY_SEQUENCE column values from the above tables by incrementing the value by +1 and the new value will be updated in the parent tables also for next child.

DEPARTMENT Table

COUNTRY Table

EMPLOYEE Table

What is the best way I can achieve this scenario? I will have a large number of records inserted in real life, So I have to take care it does not generate duplicates.

One option I have is to query the value from parent and perform child insert & parent update. Is there any good way to do this?

Answer 1

That doesn't make much sense to me because

• what you really need is master-detail relationship (department and country masters, employee detail with foreign keys establishing referential integrity constraint towards master tables)
• why do you care about order of creation (especially as you can later rearrange those values?). Besides, MAX + 1 will fail sooner or later in a multi-user environment as two (or more users) will fetch the same value and - if that "sequence" number is supposed to be unique - all other users will suffer from DUP_VAL_ON_INDEX
• that's why you should abandon that idea and - if you MUST know the sequence, use exactly that - a sequence (database object) which will ensure you have unique values, but not necessarily gapless (who cares?)