Hello, I have a question on normalizing: lets say i have three tables table1, table2 and table3 1 has many records in 2 and 2 has many records in 3. If i normalize it right, i can get records in table3 only through a join with table 2. i can imagine this is not the best solution for speed and simplicity Could it be smart to include the primary key of table 1 also in table 3, so that there is also a direct connection between the 2 or is this a real bad praktice? are there developers out there who choose to do so? Would love to hear your opinions regards, Remco -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
If you put the primary key of table 1 also in table 3, your associations become less useful, perhaps useless. Fortunately ''has_many :through'' gives you what you want without messing up your associations. ModelA has_many :model_bs has_many :modelcs, :through :model_b ModelB belongs_to :model_a has_many :model_cs ModelC belongs_to :model_b This way you can refer to ModelA.model_cs directly, and Rails will do the work of putting the right information into the SQL call. --f On 11/9/06, Remco <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> > Hello, > I have a question on normalizing: > lets say i have three tables table1, table2 and table3 > 1 has many records in 2 and 2 has many records in 3. > > If i normalize it right, i can get records in table3 only through a join > with table 2. i can imagine this is not the best solution for speed and > simplicity > > Could it be smart to include the primary key of table 1 also in table 3, > so that there is also a direct connection between the 2 or is this a > real bad praktice? > > are there developers out there who choose to do so? > > Would love to hear your opinions > > regards, > Remco > > -- > Posted via http://www.ruby-forum.com/. > > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
This is not really related to Rails, but is a more generalized DB question. An the answer is that it all depends. Yes you should normalize the tables, and yes you have to go through join tables to get the data you want. But that is what relational databases are all about, that is their job. Relational databases have been around a long time and all that kind of code has been optimized, so you should get a fast response. I use Oracle a lot and I know that having the right indexes set-up is the key to getting a query to work efficiently. I guess it would be similar for other databases. If you have lots of data, you maywant to start to de-normalize the data. But in my mind that does not really extend to putting extra keys on join tables. A good example of de-normalisation is if you want say a count of how many invoices were paid within x days. You have an invoice issued date and an invoice paid date, so you need to calculate how many days between the 2 dates everytime you want to do the query how many invoices paid within x days. If the query is infrequent or there are only a few invoices it is not a problem. But when the query frequency goes up or the number of invoices is huge then you denormalize the data by storing on the invoice how many days to took to be paid. Keeping a normalized database makes it easier to maintain and is clearer to understand. Only compromize by denormalization where there is a clear case of benifits. Andrew Remco wrote:> Hello, > I have a question on normalizing: > lets say i have three tables table1, table2 and table3 > 1 has many records in 2 and 2 has many records in 3. > > If i normalize it right, i can get records in table3 only through a join > with table 2. i can imagine this is not the best solution for speed and > simplicity > > Could it be smart to include the primary key of table 1 also in table 3, > so that there is also a direct connection between the 2 or is this a > real bad praktice? > > are there developers out there who choose to do so? > > Would love to hear your opinions > > regards, > Remco-- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
dblack-TKXtfPMJ4Ozk1uMJSBkQmQ@public.gmane.org
2006-Nov-09 13:26 UTC
Re: always completely normalize your datamodel?
Hi -- On Thu, 9 Nov 2006, Remco wrote:> > Hello, > I have a question on normalizing: > lets say i have three tables table1, table2 and table3 > 1 has many records in 2 and 2 has many records in 3. > > If i normalize it right, i can get records in table3 only through a join > with table 2. i can imagine this is not the best solution for speed and > simplicity > > Could it be smart to include the primary key of table 1 also in table 3, > so that there is also a direct connection between the 2 or is this a > real bad praktice? > > are there developers out there who choose to do so? > > Would love to hear your opinionsMost ActiveRecord databases are probably best described as paranormalized :-) It''s unlikely to be bad to aim for a fairly high degree of normalization, though; and in some respects ActiveRecord will give you some support. In your example, it sounds like you could use a :through-modified association: class Table1 has_many :table_3s, :through => :table2 and so forth. The speed should be OK; when you do: @table1.table3_s it''s all done with one query. David -- David A. Black | dblack-TKXtfPMJ4Ozk1uMJSBkQmQ@public.gmane.org Author of "Ruby for Rails" [1] | Ruby/Rails training & consultancy [3] DABlog (DAB''s Weblog) [2] | Co-director, Ruby Central, Inc. [4] [1] http://www.manning.com/black | [3] http://www.rubypowerandlight.com [2] http://dablog.rubypal.com | [4] http://www.rubycentral.org --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---