Our problem is that we have a complicated structure of elements of a large amount and need to devise an efficient solution to search and store 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.

Hi,
Well first describe and create a logical model, to assure you have the correct relationships / design.
Then.. secondarily... you can worry about performance and other things.


Let me 'rephrase' what you said
For every instance of X there are unlimited 'sets'.
I take this to meant there is a one to many relationship between entity 'X' and entity 'Set'.

For every 'set' there are unlimited Y's.
This again states there is a one to many relationship of 'Set' to entity 'Y'

Every Y has 3 properties
This simply states that entity 'Y' has attributes 1, 2, 3.

Searchable items..... The beaty of a relational database and SQL is that anything/everything is searchable. I take this to mean you want indexes on these things. An index on entity 'Y' is most likely its primary key. You can create non-unique indexes on its attributes, 1,2, 3 as well if you like

HOWEVER you have not done a very good job of describing the relationships from both perspectives. Remember every relationship (and coin) has two sides. It is important to ask both sides. So you should ask or confirm... For each set, there is one and only one instance of 'X'. Also confirm if a (an instance) 'Set' can exist without an instance of 'X'.

I made some assumptions here. I attached a gif of a logical model.

Hope that helps
Vmusic
(Praise God for Data)

Hey Vmusic, thanks for the reply. A couple questions for ya.

Could you point me in the direction of a logical model example. What do I show here? what is required? The PDF I had made... is that a logical model?

++++++++++++++

As far as your 'rephrasing'
For every instance of X there are unlimited 'sets'.
I take this to meant there is a one to many relationship between entity 'X' and entity 'Set'.

For every 'set' there are unlimited Y's.
This again states there is a one to many relationship of 'Set' to entity 'Y'

Every Y has 3 properties
This simply states that entity 'Y' has attributes 1, 2, 3.

All the above are true.

++++++++++++++

For each set, there will be only one instance of X. A Set cannot exist without a instance of 'x'

However, each Y can be referenced by more then one X.

++

When data is submited, a Set will always contain a unique string, therefore it will always be unique. But each Y follows a strict guide, so chances are data in a newly submited Y will already exist. So each Y can be called from multipe sets.

I am thinking of having one table for X's on table for Y's and one table for sets. The sets table will have a composite primary key consisting of X_id and Y_id

any suggestions?