I have a database full of recipes, one recipe per row. I need to
store a bunch of arbitrary "flags" for each recipe to mark various
properties such as Gluton-Free, No meat, No Red Meat, No Pork, No
Animals, Quick, Easy, Low Fat, Low Sugar, Low Calorie, Low Sodium and
Low Carb. Users need to be able to search for recipes that contain
one or more of those flags by checking checkboxes in the UI.
I'm searching for the best way to store these properties in the
Recipes table. My ideas so far:
1. Have a separate column for each property and create an index on
each of those columns. I may have upwards of about 20 of these
properties, so I'm wondering if there's any drawbacks with creating a
whole bunch of BOOL columns on a single table.
2. Use a bitmask for all properties and store the whole thing in one
numeric column that contains the appropriate number of bits. Create a
separate index on each bit so searches will be fast.
3. Create an ENUM with a value for each tag, then create a column that
has an ARRAY of that ENUM type. I believe an ANY clause on an array
column can use an INDEX, but have never done this.
4. Create a separate table that has a one-to-many mapping of recipes
to tags. Each tag would be a row in this table. The table would
contain a link to the recipe, and an ENUM value for which tag is "on"
for that recipe. When querying, I'd have to do a nested SELECT to
filter out recipes that didn't contain at least one of these tags. I
think this is the more "normal" way of doing this, but it does make
certain queries more complicated - If I want to query for 100 recipes
and also display all their tags, I'd have to use an INNER JOIN and
consolidate the rows, or use a nested SELECT and aggregate on the fly.
Write performance is not too big of an issue here since recipes are
added by a backend process, and search speed is critical (there might
be a few hundred thousand recipes eventually). I doubt I will add new
tags all that often, but I want it to be at least possible to do
without major headaches.
Thanks!