Is there database design pattern that calls for the auxiliary table to store sequence numbers and how else can be applied?

I have table: blocks: id integer not null primary key, ref_date date not null, seq_no integer not null, lk_document integer not null there can be multiple records for each lk_document and all those records should have seq_no assigned according to ref_date asc id asc order in the dense manner, i.e. 1, 2, 3... without gaps. I.e. 1, 2, 4, 6 is not allowed. Is there design pattern for the DB schema design that organizes how seq_no is updated upon the changes of ref_date (including upone the insert or delete of records, which, of course, have ref_date and which can leave open gaps). I feel, that the robust solution would be to more seq_no into the separate table: blocks_ext id integer not null primary key (possible referent to blocks.id) seq_no integer not null While I see a lot of advantages and the solution can be pretty robust (maintained by the after update/insert/delete triggers on the blocks table), I am not sure if this is the design pattern and is the use case to serious as to create separte table. Maybe it is still better keep seq_no in the blocks table and come up with some ingenuity how to maintain it with the guarded before update/indert/delete triggers. From the one side the ordered list of seq_no is the aggregate over the set of blocks record selected by lk_document and such aggregate does merit its own table or the field in the documents table (which may keep the ordered list or blocks.id entries, finite list only allowed). From the other side - I am still hesitating is this known, recognized pattern, best approach. And is it used in other cases as well? I am using Firebird SQL database (very old version 2.1 to be specific), but I guess - my question is general enough for the design in any SQL database. Though, more advanced databases can have triggers on 'after transaction complete' and such triggers can write back (is this so indeed?) seq_no in the same table blocks? **Note added:** And there is still another aspect of this design using auxiliary table. My original table can have record_version field blocks.rv which increases (from some sequence) with every update of the record (and also with every insert). Now there can be situations when there is update on blocks_ext.seq_no due to changes in other records. So - I should add rv to blocks_ext as well and I should always select the updates on the blocks by the: select b.id from blocks b left join blocks_ext e on (b.id=e.id) where maxvalue(b.rv, e.rv)>:rv_of_last_update But I guess, nothing to be done here. And this type of select will be require complete scan of blocks/blocks_ext, there are no index on such condition. **Note added 2:** I guess, this solution - with the auxiliary table, is the materialization of aggregation (ordering) essentially and materialization is OK if done for the performance purposes. Regarding rv - it may be the most efficient to keep blocks_ext.rv only and update both in the case when the sequence is being updated (blocks_ext.seq_no changes) abd by the blocks rv insert/update tiggers.