Well...one of the joys of development is recognizing a design that was perfect...is less so in a given context.  And what I thought was an elegant & correct solution appears to not lend itself to answering the questions I'm now asking.  So...I'm asking for suggestions on my next revision.

My current structure:

A parent table

CREATE TABLE NODES
(
  NODE smallint NOT NULL,
  CONSTRAINT PK_NODES PRIMARY KEY (NODE)
);

A child table

CREATE TABLE ROUTES
(
  NODE smallint NOT NULL,
  ROUTE_INDEX smallint NOT NULL,
  LINK_NODE smallint NOT NULL,
  QUALITY smallint,
  CONSTRAINT PK_ROUTES PRIMARY KEY (NODE,ROUTE_INDEX)
);

ALTER TABLE ROUTES ADD ROUTE_PK COMPUTED BY (cast(NODE as char(4)) || cast(ROUTE_INDEX as char(1)));
ALTER TABLE ROUTES ADD CONSTRAINT FK_ROUTES_NODE
  FOREIGN KEY (NODE) REFERENCES NODES (NODE) ON UPDATE CASCADE ON DELETE CASCADE;

And an archive table

CREATE TABLE ROUTE_HISTORY
(
  NODE smallint NOT NULL,
  ROUTE_INDEX smallint NOT NULL,
  LINK_NODE smallint NOT NULL,
  QUALITY smallint,
  ROUTE_UPDATED timestamp NOT NULL,
  CONSTRAINT PK_ROUTE_HISTORY PRIMARY KEY (NODE,ROUTE_INDEX,ROUTE_UPDATED)
);

ALTER TABLE ROUTE_HISTORY ADD CONSTRAINT FK_ROUTE_HISTORY_NODE
  FOREIGN KEY (NODE) REFERENCES NODES (NODE) ON UPDATE CASCADE ON DELETE CASCADE;
CREATE UNIQUE INDEX IDX_ROUTE_HISTORY_LIST ON ROUTE_HISTORY (NODE,ROUTE_UPDATED,ROUTE_INDEX);

The above structure worked perfectly...until I wanted to identify, filter, or delete duplicate entries. Because a "true" duplicate required matching across multiple rows this became...at best rather complicated. So I think I need to "flatten" my structure - I'm just not sure of the "best" method.

Please keep in mind the following:

1. Each NODE can have from 0 to 8 ROUTES active at any time.

2. ROUTES get updated anywhere from every few seconds to a few weeks depending on real-world activity.

3. The "detail" fields shown are a subset - there are actually 5 more (though they are all of type SMALLINT if that makes any difference).

4. I'm still trying to decide how to handle duplicate entries in the archive table. At a minimum, I need to be able to identify duplicates easily for filtering or search purposes. As long as that is simply obtained I can decide to either block duplicates in the history pre-insert or filter them later for display purposes. The purpose of the history is to analyze short-term changes - not find exceptions in long-term stability. So I'll probably wind up blocking duplicates pre-insert...

At first glance it seems I have a few options:

1. Experiment with Firebird array datatypes...but while I was able to create one in Flamerobin, I couldn't insert data. So I'm guessing this is still in the development/experimental area?

2. Implement my own array - use a CHAR or VARCHAR to store the info. Either using known lengths or delimited values.

2a. Next decision - flatten the entire "ROUTE" structure into a single long VARCHAR - or have a simulated array for each detail field.

2b. Computed fields - I could define a field using SUBSTRING for each individual detail...but I don't think I have any reason to operate with an individual route's detail field at the database level. I'll be passing "complete" route sets to/from the application - so this is probably a waste of time.

3. Create a group of singleton tables - Route_1, Route_2,..., Route_8 - with all the detail fields. A join would pull them together to build complete route. This would theoretically potentially maybe sort of reduce storage requirements - except now additional indexes and processing would be required. And real-world analysis tells me the majority of nodes will have more than 5 routes active - so this is probably a lot of effort for either little gain or actually a net loss in performance.

4. Probably the worst choice - have a table with individual fields for every route+detail, e.g., Route_1_Quality, Route_4_Linknode. I don't even like thinking about this one but now that I've typed it I'll leave it as an example of A Bad Thing.

5. Something brilliant from a member of this discussion group that I haven't thought of. I'm holding out for this one.