Representing an array for best performance

[Margaret MacDonald]

I'm not a database person, so the answer to this might be obvious to everyone but me!

For the best performance, how should I represent an array for lookup?

I'm implementing interlocking trees, in which some leaf C can be the child both of branch A and branch B. Right now I'm encoding the relationship in a shared token: C has tokens for both A and B as substrings in a varchar field. So my SELECT would be LIKE "%sometoken%". But the other day in the documentation I ran across a note that says a LIKE with a leading % doesn't use indexing, it uses a brute-force search. That made perfect sense when I came to think about it (which til that point for some reason I hadn't :-( ) But that leaves me in the dark about how best to represent the relationships for the fastest lookup.

I've thought of creating a relationship table (I'm sure there's a technical name for it) that would have one record for the AC relationship and one record for the BC relationship. But then, to select out all the kids of B, I'd have to read up all the BC relation records, build an IN list from them, and then do the read I actually want. Which seems like it would also be very expensive.

Is there a better way? Thanks in advance for any insights!