Re: What's the best way in Postgres9 to store a bunch of arbitrary boolean values for a row?

On 12/05/2011 12:10 PM, Mike Christensen wrote:
> 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.
I'd use hstore to store them as tags. You can then use hstore's GiST
index support to get quick lookups.
> 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.
It'll get frustrating as you start adding new categories, and will drive
you insane as soon as you want to let the user define their own
categories - which you will land up wanting to do in your problem space.
I'd avoid it.
> 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.
Same as above, it'll get annoying to manage when you want user tagging.
> 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.
Same again.
> 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.
That'll get slow. It'll work and is IMO better than all the other
options you suggested, but I'd probably favour hstore over it.

--
Craig Ringer