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)_idChildren table:
id, grandchild1_id, grandchild2_id,... grandchild(100)_idmodels:
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 :childMy 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.
精彩评论