TripleSpeeder•8mo ago

index on string field - how to handle case (in)sensitivity?

I have a minor issue where I'm not sure whats the best approach. I have a simple "manufacturer" table with a name field. I frequently need to select entries based on name, so have an index on the name field. Enduser can create new entries, but the name should be unique. So when the user tries to create a new entry, the first thing I'm looking at is existing entries with that name:

// prevent creation of duplicate manufacturer
    const existingManufacturer = await db
      .query("manufacturer")
      .withIndex("byName", (q) => q.eq("name", manufacturerData.name))
      .unique();
    if (existingManufacturer) {
      // use existing entry...
    } else {
      // create new entry...
    }

// prevent creation of duplicate manufacturer
    const existingManufacturer = await db
      .query("manufacturer")
      .withIndex("byName", (q) => q.eq("name", manufacturerData.name))
      .unique();
    if (existingManufacturer) {
      // use existing entry...
    } else {
      // create new entry...
    }

Now I need to handle case mismatch. If I have an existing entry like Special Brand and the user tries to create a new one in lowercase special brand it should use the existing entry. Naive approach is to convert everything always to lowercase, so the database only contains lowercase names and any string provided by enduser gets converted to lowercase before processing. But since I'm dealing with brand names I need to preserve casing, as that may be a part of the brand identity. E.g. names like "bigBETty" should be stored exactly. My current idea to handle this is to store the name twice - One lowercase variant that is used for indexing and searching, and one "display" variant that is only used for display purpose and keeps the exact spelling/casing. Does this make sense? Or are there better approaches?

2 Replies

Michal Srb•8mo ago

Your approach is correct. 👍

TripleSpeederOP•8mo ago

Nice, thank you for the confirmation 🙂