MODX Extra: searching a JSON string row - yay or nay?

This is quite a more technical question than it is a MODX question, so bare that in mind when you read it.

Hey guys,

For now, I can’t go into the specifics of the project but I guarantee that it’ll be released to the public in a short while. So, the question! Here it comes.

I’ve got a dynamic table which hold a e-mail, which needs to always be unique and a credentials row which contains a JSON encoded string with (assoc array keys), among many, firstname, lastname, website, twitter and other. Now, you could easily understand that this table is pretty hard to search with a query since the credentials is a TEXT type row with an insane amount of data with is unreadable/unqueryable by any database engine. You can’t simply query "... AND `credentials` LIKE "%query%"..." since if "query" would contain "firstname" instead of the real first name, it would return EACH row. This is very, very undesirable.

Now, imagine each credentials row holds a JSON encoded array with, when decoded, 20 associative keys and values. My idea was, to make it better searchable, is to create a separate table which contains the ID of the user (in this case), the associative key and the value with a total of 4 columns (id, userid, key, value). So, if one "list" (for the lack of a better word) hold 1000 users (which it will!) with a configured credential database of 20 keys, for only ONE "list", it’ll contain 20000 rows of data, simply to make it searchable!

So the question is, is it preferred to make it -that- much more searchable (and if so, is my example a valid method to use this? Imagine this database to grow to about 200.000 to 500.000 rows after 1 year or use.) or is this giant use of overhead total bull when it comes down to searching (I reckon there will be more maintenance than real searching for specific users, as you can already search on the e-mail address)?

Please help me out!

If searching and sorting are important in your design, you could just create another table with the fields/indexes explicitly defined as well, though that wouldn’t work if the additional fields are ad hoc and not pre-determined. Otherwise, you have a perfectly valid design with the data consolidated in one field for quick retrieval and minimal storage in one table, and expanded for searching and/or sorting, as long as you can define reasonable limitations on the size of the value field (which I assume would have to be a string with at least 255 bytes, not necessarily good for numeric or date searching/sorting).

Thanks Jason. In this case I’ll then be sticking with the good old single row with all available data. If however in the future people want an advanced search, I can always convert it to their wishes. Perhaps even then I can make it so that they can specify which keys are searchable by just checking them in the backend.

Thanks again.