Design question

[Arthur Clemens]

I am encountering a basic design question - and I find it difficult to 
put into words. Let me describe my application in progress:

I am creating a database for a Slovene-Dutch dictionary. I have created 
a hierarchical design for this, that I think works out well, but does 
creates some difficulties.

database tables:
	Record
		child SloveneWord ( n x)
		child FormalDescription (with date fields) (1 x)
			child RecordSection (n x)
				child DutchTranslation (n x)
				... a few more tables (n x)


In the interface (UI), I have a scrollable table of Slovene words - 
clicking on a word shows the full record. The table can be sorted on 
word (ascending and descending) (SloveneWord.text), on translation 
(DutchTranslation.text), and on modification date 
(FormalDescription.modification_date).
When I perform a search, the results set narrows with each letter that 
I type, thereby keeping its sort order.

The basic question is: how do I create a set that I can use to 
accomplish this?
I have several options, but I am not sure what is a good choice design 
wise or performance wise.

1)
Create a FBL_BitSet of Record->SelectAllRecords(), then create an 
FBL_ArraySet out of this.
I cannot just create a set of SloveneWord.RecIDs, as empty entries can 
exist (a Record with nothing filled in for SloveneWord) - these should 
be visible as empty lines in the UI table.
Problem: how to sort Record.RecIDs on the basis of a field in a child 
table?
Possible solution:
For each Record.RecID:
	- Find a SloveneWord where its parent equals the Record.RecID, and its 
sort_order is 0.
	- Of the found SloveneWord, feed its SloveneWord.text to a compare 
function.
I can use the same principle to get FormalDescription.modification_date 
when sorting on date.
Is this super slow? Is there a better way?

2)
Create a FBL_Cursor from a SqlSelect query:
         "SELECT record.RecID, slovene_word.parent, 
slovene_word.sort_order "
         "FROM record, slovene_word "
         "WHERE slovene_word.sort_order = 0"

But this does skip empty SloveneWord (NULL) entries. And I think it is 
not possible to create a set from a cursor.

3)
When sorting on SloveneWord, create a FBL_ArraySet using Sort( 
allMySloveneWordsBitSet, slovene_word.text, ascending == true);
and somehow map it to the BitSet of all Records.
Seems far fetched.

4)
Create an intermediary M::M table: Record_SloveneWord_Table, to keep 
track of each Record and its corresponding SloveneWords children.

Record_SloveneWord_Table:

|	record.RecID		|	slovene_word.RecID	|
-------------------------------------------------
|			1			|			1			|
|			2			|			2			|
|			2			|			3			|
|			3			|			4			|
|			4			|			5			|
|			4			|			6			|
|			4			|			7			|


But to sort this I would still need a sorting function like in 1).


Any help and suggestions are welcome!



Arthur Clemens