SET data type in MySQL and ActiveRecord

I figured out something cool which solves a common database design problem. However, I’m not sure if this is supported in ActiveRecord since the SET data type does not exist in all databases.

Let’s say each record in a table has zero or more properties and your set of properties is pre-defined.
How would you implement this in MySQL? Maybe it’s possible to do what I’m about to say in other databases as well? If so, please comment.

Use the SET data type in MySQL to solve this problem. Imagine you have a bunch of checkboxes in your HTML form which define the properties of each record of your table.

By using the SET data type, you can simplify your database design and, ultimately help increase performance too!

mysql has a SET data type. You can define what properties you want for a particular column and it will use only one bit per property. For example, each column in the Cars table can store one or more of these attributes. I believe in the database, it’s stored as a binary field of 1’s and 0’s like 1010100000 and the 1s and 0s are mapped to the SET we defined when we created the table!

Try out the following six statements in MySQL:

CREATE DATABASE test;
CREATE TABLE car ( option SET ('sports', 'sunroof', 'spoiler', 'turbo', 'leather', 'power-steering', 'diesel') );
INSERT INTO car (option) VALUES ('sports,turbo,power-steering');
INSERT INTO car (option) VALUES ('leather,power-steering,diesel');
INSERT INTO car (option) VALUES ('spoiler,diesel');
SELECT * FROM car;

Be sure you don’t put any spaces after the commas in your INSERT statements!

How do you use the SET data type in ActiveRecord? I haven’t used this yet in a real world application yet. Once I figure out how to use it with ActiveRecord, I will update this entry. In the meantime, feel free to post comments and examples.

Thanks!