CRUD Searching

Published 2021-04-11 20:07

Here's my philosophy on searching a CRUD app (well, at least for the app I've been developing since this month one year ago).

First, a few of the primary data structures:

[Users]
[Units]
[Documents]
[Work Items]

Document

Work Item

The most common thing to search for is a Unit by its name/identifier (note: not in the 'unit of measure' sense. Unit is a transportation asset in this context).

The search UI (user interface) starts out just as a text input at the top of the viewport. But as you type, a constellation of other UI elements appear from the mist.

Search Input Screenshot

From observation, the user search behaviour (from most common to least):

  1. Type a Unit name and either hit Enter key or click the generated (upon each key-press) link of their typed string—Enter or link execute the same action.
    (it's not uncommon for the typed name to merely approximate the canonical one—Foo1 instead of Foo01, or adding a space between the alphas and numerics)

  2. Type Unit name, then click generated alternative suggestion link.
    (suggestions: @unit:foo01 | @wo:foo01 | @unit:"foo01" | @wo:"foo01")
    (@unit and @wo are tags, part of a DSL (domain specific language) to perform a narrower query on the backend.)
    (more than two tags are available to hand-craft a search, but @wo is the other one elevated to a suggestion because Work Order is the second-most searched target.)

  3. (No typing, second round) Try a different alternative link than the first one (presumably because either no results or too many irrelevant ones arrived).

  4. (No typing, second round) Click the permalink of the typed search (materializes after a search is executed). (More rarely used than I would have guessed; the feature affords keeping a bookmarkable search in a separate tab for repeated use. I suppose most searches are effemeral, and who saves google/duckduckgo/bing(just kidding) searches? So fair enough.

To clarify: All searches are case insensitive. All simple searches are substring matching.

Now, an interesting tradeoff to consider:

Should a plain search and a tagged search exhibit the same or a different level of exact/fuzzy matching?

"Same Matching":
Pro: Simpler, more predictable UX (User eXperience).
Con: Less powerful (precludes more nuanced, advanced use).

I went with less consistent, but more pow-er-ful  "Different Matching". And now I will defend my hot take.

Plain search (foo1): Foo01 will match
Tagged search (@unit:foo1): Foo01 will NOT match

First, about how the fuzzy matching is accomplished to begin with.

Fuzzy Pomeranian Dog
ATTRIBUTION: lexky

Database Unit.name column: Foo01
Database Unit.alias column: Foo1,Foo 1,Foo 01,Big Foo,Foo02,Foo2,Foo 02,Foo 2

The user can stuff the alias column with a bunch of permutations to increase the Power of Fuzzy™.

This approach also helps with the paired Units case. In the domain there are tandem Units identified like this:
Foo01/02, Bar15/16, etc.

Sometimes they are treated as a single entity, other times separately (business logic is fun!). A document can be created for Foo01/02 or Foo01 or Foo02 (each of these is its own record in the Units table; the later two get an attribute of component).

In a simple search a user looking for a Foo01/02 doc might just type Foo02—oops, this will NOT match by name... but it will match by alias, so yay!

So where we're at:
(recall plain searches query all searchable fields including unit name and unit alias, whereas a tagged search will only query a particular field (@unit looks at unit name).

Plain Search (foo01): All Foo01/02, Foo01, and Foo02 docs match.
(A plain search queries all searchable fields—unit alias, free-form textarea in Documents and related Work Items fields, and more.)

Tagged Search (@unit:foo01): Foo01/02 and Foo01 docs match.

Tagged Search (@unit:foo02): Only Foo02 docs match.

Tagged Search (@unit:foo01/02): Only Foo01/02 docs match.

Tagged searches are more descriminating, want to join The Finer Things club at work, and this can be helpful in excluding docs. Like when foo02 is by chance mentioned in a description field but you only want that doc if the Unit field is actually Foo02.
The downside: you will also exclude Unit field is Foo01/02 or Foo01 because Foo02 is not a substring of either, and your intention may be to cast a wider net.

Based on usage patterns to date here's the bottom line:

The casual user is typically fishing for anything Foo 01 / 02 related, so they get the widest query result with a plain search, and since they tend to use plain searches over complex ones, the miminal search returning the maximal results is a good fit.
Incidently, the most recently updated docs are ordered first in the results, as the user is most often looking for the recent in-progress docs, not the older, finished ones.

The power user will inevitably need more specificity at certain times, and that's where offering alternative searches ("prefab'd" tagged searching) help with the immeadiate need and hopefully acts as a gateway to learning what advanced searches look like and how they operate.

Now consider (@unit:foo01): Foo01/02, Foo01 both match.

Sure, but what about when the power user wants to be even more specific: "Bring me the only the non-paired (Foo01) docs!"
This is were the quoted version of the key-value (@tag:value ) comes in.

Search(@unit:"foo01"): Only Foo01 docs match.

@tag:value matches on a field substring.
@tag:"value" matches on the whole field string only.

The DSL also has AND and OR operators, so you can build multi-term searches and those can be bookmarked just as easily.
Maybe... janedoe+@unit:Foo01/02+@begin:2020-08+@end:2020-12 but I think we've done enough damage here already.

And there you have it. If you waded through all that foo and it made sense, congratulatons, you now have some idea how my brain is wired.