Our problem is that we have a complicated structure of elements of a large amount and need to devise an efficient solution to search through them.


See image. http://www.vekdeer.com/posts/database_architecture.pdf
For every instance of X there are unlimited 'sets'.
For every 'set' there are unlimited Y's.
Every Y has 3 properties
Searchable items are 1, 2, 3, and Y's.


Our first idea was to have a table for each possible Y value of all the X's combined.
E.g. a table for Y1, a table for Y2.

Each entry in these tables has it's unique properties and ID that would match that of an entry in the X Table. If someone searched for Y1, all entries in the Y1 table would match their entries in the X table and output them.

We thought this would be more efficient than storing everything in one table and having to scan the entire table searching for entries of Y1.
Due to the unlimited possible number of Y instances per X, they would have to be stored in the same cell and parsed on search, then matched against the searched term.

It should be noted that the number of X terms has the possibility to exceed 200,000 entries, yet the possible Y values will not likely exceed 250.