Ruby on Rails: Saving multiple values in a single database cell

Question

How do I save multiple values in a single cell record in Ruby on Rails applications?

If I have a table named Exp with columns named: Education, Experience, and Skill, what is the best practice if I want users to store multiple values such as: education institutions or skills in a single row?

I'd like to have users use multiple text fields, but should go into same cell record.

For instance if user has multiple skills, those skills should be in one cell? Would this be best or would it be better if I created a new table for just skills?

Please advise,

Thanks

Which database are you targeting?

mu is too short
– mu is too short

2013-09-09 04:34:01 +00:00
Commented Sep 9, 2013 at 4:34 — mu is too short
– mu is too short, Commented Sep 9, 2013 at 4:34

Rajesh Kolappakam · Accepted Answer · 2013-09-09 04:30:30Z

8

I would not recommend storing multiple values in the same database column. It would make querying very difficult. For example, if you wanted to look for all the users with a particular skill set, the query would clumsy both on readability and performance.

However, there are still certain cases where it makes sense.

When you want to allow for variable list of data points
You are not going to query the data based on one of the values in the list

ActiveRecord has built-in support for this. You can store Hash or Array in a database column.

Just mark the columns as Text

rails g model Exp experience:text education:text skill:text

Next, serialize the columns in your Model code

class Exp < ActiveRecord::Base
  serialize :experience, :education, :skill
  # other model code
end

Now, you can just save the Hash or Array in the database field!
```
Exp.new(:skill => ['Cooking', 'Singing', 'Dancing'])
```

answered Sep 9, 2013 at 4:30

Rajesh Kolappakam

2,12514 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

hellomello Over a year ago

Why is it that I get an argument error when I have serialize like that in my model?

hellomello Over a year ago

there's a pretty long full trace error. is there something that I should be looking for? Or maybe just application trace which is this ->

app/models/exp.rb:3:in '<class:HairExp>' app/models/exp.rb:1:in '<top (required)>' app/controllers/exp_controller.rb:1:in '<top (required)>'

hellomello Over a year ago

sorry it should be Exp not HairExp

hellomello Over a year ago

Maybe an easier way is if I create a new table for each subject? a table for skill, a table for experience, another table for education? Would this be better in the long run?

Rajesh Kolappakam Over a year ago

Yes, that is absolutely the best approach. It would make things much more easy to maintain, e.g. you can add new data points later on, you can query by these attributes, etc.

Community · Accepted Answer · 2017-05-23 11:45:55Z

1

You can do it using a serialized list in a single column (comma-separated), but a really bad idea, read these answers for reasoning:

I suggest changing your schema to have a one to many relationship between users and skills.

edited May 23, 2017 at 11:45

CommunityBot

11 silver badge

answered Sep 9, 2013 at 4:19

rails_id

8,2204 gold badges48 silver badges86 bronze badges

Comments

hawk · Accepted Answer · 2013-09-09 04:01:25Z

0

Rails 4 and PostgreSQL comes with hstore support out of the box, more info here In rails 3 you can use gem to enable it.

answered Sep 9, 2013 at 4:01

hawk

5,4082 gold badges25 silver badges29 bronze badges

1 Comment

hawk Over a year ago

@j03w Thank you! Yes arrays are also supported.

techvineet · Accepted Answer · 2013-09-09 04:40:23Z

It depends on what kind of functionality you want. If you want to bind the Exp model attributes with a form (for new and update operations) and put some validations on them, it is always better to keep it in a separate table. On the other hand, if these are just attributes, which you just need in database keep them in a single column. There is way by which you can keep the serialized object like arrays and hashes in database columns. Make them a array/hash as per your need and save it like this.

http://api.rubyonrails.org/classes/ActiveRecord/AttributeMethods/Serialization/ClassMethods.html#method-i-serialize

Serialized attributes, automatically deserializes when they are pulled out of tables and serialized automatically when saved.

Collectives™ on Stack Overflow

Ruby on Rails: Saving multiple values in a single database cell

4 Answers 4

5 Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related