Multiple foreign keys in parent records (Rails)

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开发者_StackOverflow 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 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

---

Are you sure you have a problem with performance? If you are only anticipating, you may be doing premature optimization.

If you are able to create this many columns as the number of children, then I am almost sure that the database design which you are trying to avoid, is not the problem. Just ensure that the database has proper indexes.

A working (and covered by tests) application is a good starting point for thinking about performance. It may happen that you notice that the performance is acceptable with the simplest approach.