I am migrating a bunch of existing ISAM files into Firebird. In order to make the migration easier for the programmers we want to be able to reproduce the ISAM structure and behaviour as closely as possible.

The ISAM files have a lot of composite keys. Consider the GL journal entry file. It has a primary key of:

Company
Account
Division
Department
Effective Date
Control Number

A typical thing that would be done in the existing programs would be to seek to some point in the file and read from there to the end of the file. So supposed you want to start at company 'CO', account 'ACCT' and read all records from that point to the end of the file. In an ISAM file this is easy to do. You set up your key by concatenating the individual fields and return all records whose key is >= to the concatenated fields. So, in this example you would return all records where the key >= 'COACCT' (which is the company and account codes strung together).

This is not so easy to emulate with a SELECT statement. I have come up with this:

SELECT *
FROM GLJOUR
WHERE Company > :Company OR
(Company = :Company AND Account > :Account) OR
(Company = :Company AND Account = :Account AND Division > :Division) OR

and so on and so on and so on ...

Another alternative would be to create a single column in the table which is the concatenation of all of the columns making up the composite key and using that as the key. That is to say, make a single column key rather than a composite key. I would still want to retain the individual key fields as separate columns in the database so I would in effect be duplicating the data. That makes the relational purist in me shudder but it would get the job done and be considerably less complicated than dealing with the composite key.

Are there any other options that I have failed to consider? If it were you, how would you handle it?