Vlad,

I want to make sure I understand the structure you propose correctly.

I think you are proposing the following tables:

Dictionary (one record for each word)
Table_to_be_searched
CrossRefs: several records for each record in Table_to_be_searched (one for each word).

Is this approximately what you had in mind?


TIA, Hilmar.


>Hi!
>
>Why split field into the several fields when you can use another table, usually called 'dictionary'?You query then will look like following:
>
>select * from musictbl inner join dict on dict.ID = musictbl.UD WHERE dict.Word = "NIGHT"
>
>Also you get rid of UPPER(), though dictionary table will be quite large that create large index anyway and some restrictions. You can also split out wards from dictionary table to save space:
>
>select * from musictbl
> inner join crossrefs on musictbl.ID = crossrefs.RecID
> inner join dict on dict.ID = crossrefs.DictID
>WHERE dict.Word = "NIGHT"
>
>In such case you will have word "NIGHT" only once in the dictionary, and all other things will be just related to it by ID. This will save some space, though updating of data is getting more tricky...