Alternatives to 'SET' data type

All,

I have some data that could be a combination of around 30 values. Typical
values could be 'TB', 'TF', 'D', 'U', '-D', 'OP', 'RM', or nil.

Ideally, I'd use the SET data type in PostgreSQL, but ActiveRecord doesn't
appear to support[1] this. More specifically, I can run a migration on my
development database, but db/schema.rb contains a comment about an
unsupported data-type.

I've considered creating 30 columns to model this, however I'm not convinced
that's efficient. I've also thought about separating each value with a :
and storing this - for example, ":TF:-D:" - whilst I can query for "WHERE
foo LIKE '%:D:%'", that probably isn't indexable as it's a text search.

Can anyone help me to come up with an efficient way to do this in Rails
3.0.10?

Kind regards,

Peter

[1] http://ionrails.com/2010/06/16/set-data-type-mysql-activerecord/

If the attributes are effectively independent, so that a record may be
a TB or it may not, and it may be a TF or it may not, and so on, then
use 30 (or whatever) boolean columns as that is the simplest way to do
it and it most closely maps to your requirement. I don't see in what
way this would be inefficient. It would surely be the most efficient
solution when it comes to finding records of particular types.

In any case you should not worry about efficiency at this stage.
Start with the most straight forward design and *if* performance
becomes an issue then optimise it later. I can virtually guarantee
that the bottleneck in a app will not be where you expect to be at the
outset, so starting with a more complex (and potentially buggy)
solution in order to work around perceived bottlenecks is rarely a
good idea.

Colin

Another solution might be to have a table of options (or whatever word
is appropriate), where option.name is 'TB', 'TF' and so on. Then you
can have a HABTM relationship between the tables and you could get all
the options for your main object using
@my_object.options
which would give you (effectively) an array of the options.

This would have the advantage of flexibility in that you can add
further options if you need to. Also it gives you somewhere to store
further information about the option. It all depends on the details
of the problem you are trying to solve.

Colin

Peter Hicks wrote in post #1027558:

I have some data that could be a combination of around 30 values.
Typical
values could be 'TB', 'TF', 'D', 'U', '-D', 'OP', 'RM', or nil.

Ideally, I'd use the SET data type in PostgreSQL, but ActiveRecord
doesn't
appear to support[1] this. More specifically, I can run a migration on
my
development database, but db/schema.rb contains a comment about an
unsupported data-type.

I've considered creating 30 columns to model this, however I'm not
convinced
that's efficient. I've also thought about separating each value with a
:
and storing this - for example, ":TF:-D:" - whilst I can query for
"WHERE
foo LIKE '%:D:%'", that probably isn't indexable as it's a text search.

Can anyone help me to come up with an efficient way to do this in Rails
3.0.10?

Yes. store this as a bit-mask. Then your query is as efficient using
integer comparison. Map your labels to the bit-mask.

Example:

Constants

That works of course, but then you may have the complication of adding
all the setters and accessors so you can say things like
widget.tb = true
do_something if widget.tb
and so on.
Once again it depends upon the details of the OPs requirement.

Colin

There are plugins that will do all of this for you... search for something like "rails bit field plugin"

-philip

There are plugins that will do all of this for you... search for something like "rails bit field plugin"

I use has bit field which is here: https://github.com/pjb3/has-bit-field

Colin Law wrote in post #1027667:

That works of course, but then you may have the complication of adding
all the setters and accessors so you can say things like
widget.tb = true
do_something if widget.tb
and so on.
Once again it depends upon the details of the OPs requirement.

Yep. I didn't say it might be a simpler solution, but the question was
"Can anyone help me to come up with an efficient way to do this in Rails
3.0.10?" That the question I was answering anyway. I think we can all
agree that a bit-field would be the most efficient. Integer comparison,
especially if the field is indexed should provide excellent query
efficiency.

But, as others have mentioned, there are gems to make this more complex
solution easier to manage.