eden li
10/7/2006 4:48:00 AM
The "tried and true" method I think you're hinting at involves exactly
what you'd like to avoid -- more tables. The best way to represent
data in a relational database is to, surprise, surprise, use relations.
By storing more than one value in a single field you're going to make
it difficult for yourself in the future if you want to do any sort of
meaningful queries on your data (esp if you have millions of records).
For example, if your database stores blog posts, and you want to enable
categories to be assigned to every blog post, this requires 3 tables:
- A table that stores posts (eg, posts(id, text))
- A table that stores all the possible categories (eg, categories(id,
name))
- A table that relates the two (eg, categories_posts(category_id,
post_id))
ActiveRecord supports this using the has_many relationship with the
:through attribute. All of the joins that would be required are taken
care of for you, and you get things like:
post.categories << Category.find_by_name('databases')
post.categories.delete(Category.find_by_name('databases'))
category.posts.each do |post|
# .. something
end
This is WAY easier than the other option which is to have a category
text field that you maintain yourself, but this causes a few problems
for maintence:
- If you want to remove a category, you must loop through every post
and do a string replace on the category text field.
- If you want to get all the posts in a given category, you have to
write a LIKE query which can be imprecise unless you're careful about
how you format the field (eg, what do you do about categories that
share the same suffix or prefix)
- It's difficult to come up with a proper list of all possible
categories -- you have to loop through every post and parse the
category field. You could keep this in a constant somewhere in your
app, but keeping this list up to date is a maintenence nightmare and at
that point you've made it difficult to add/remove categories on the fly
without requiring knowledge of Ruby.
- etc, etc... have I made my point yet?
I'm not against storing multiple values in a single field -- sometimes
its the most practical and expedient method, especially if none of the
above situations apply. However, if you're looking for the "tried and
true" (which I take to mean scalable and clean) method, something like
the above is something you should consider.
Dark Ambient wrote:
> I was wondering if there was a tried and true method. I'd imagine this is a
> fairly common thing with forms and databases.