Multiple foreign keys in parent records (Rails)

Question

I'm trying to get rid of my join tables, in favor of a database design that is faster for large datasets. The way I'm planning to do this is to store the id's of children in the parent record. Like so:

Parents table:
id, child1_id, child2_id, child3_id,... child(100)_id

Children table:
id, grandchild1_id, grandchild2_id,... grandchild(100)_id

models:
Parent
has_many :children, :dependent => :destroy
accepts_nested_attributes_for :children
Child
belongs_to :parent has_many :grandchildren, :dependent => :destroy
accepts_nested_attributes_for :grandchildren
Grandchild
belongs_to :child

My end result would be to be able to edit parent, children and grandchildren in the same form. This is already possible by using join tables, but I'm keen on boosting up performance

How can I get better performance for my database, when I depend on the functionality join tables provides? Please help, I've been searching the web for many days now :/ Thanks!

This is a terrible design. Whilst it may speed up read/write for one case, it limits it to that - not only that, but, its not normal. Surely a non-relational database would be better if this really is your aim. — Omar Qureshi

Yule Yule · Accepted Answer · 2011-05-23T12:51:52

This is terrible database design. What if a parent has more than 100 children? 3 suggestions:

Consider using NOSQL database store.
Perhaps use acts_as_tree.
Could you not normalize it as such:

Parent: id

Child: id, parent_id

Grandchild: id, child_id

Multiple foreign keys in parent records (Rails)

2 Answers