# Always completely normalize your datamodel? **URL:** \ **Category:** Rails\ **Created:** [November 9, 2006, 12:59pm UTC](https://www.ruby-forum.com/t/always-completely-normalize-your-datamodel/75365 "2006-11-09T12:59:41Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![remco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/9d8465/32.png) [@remco](https://www.ruby-forum.com/u/remco)\ **Post date:** [November 9, 2006, 12:59pm UTC](https://www.ruby-forum.com/t/always-completely-normalize-your-datamodel/75365/1 "2006-11-09T12:59:41Z")
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 ---
**Author:** ![remco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/9d8465/32.png) [@remco](https://www.ruby-forum.com/u/remco)\ **Post date:** [November 9, 2006, 1:22pm UTC](https://www.ruby-forum.com/t/always-completely-normalize-your-datamodel/75365/2 "2006-11-09T13:22:28Z")
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 ---
**Author:** ![remco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/9d8465/32.png) [@remco](https://www.ruby-forum.com/u/remco)\ **Post date:** [November 9, 2006, 2:05pm UTC](https://www.ruby-forum.com/t/always-completely-normalize-your-datamodel/75365/3 "2006-11-09T14:05:17Z")
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 ---
**Author:** ![remco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/9d8465/32.png) [@remco](https://www.ruby-forum.com/u/remco)\ **Post date:** [November 9, 2006, 2:28pm UTC](https://www.ruby-forum.com/t/always-completely-normalize-your-datamodel/75365/4 "2006-11-09T14:28:52Z")
Hi – On Thu, 9 Nov 2006, Remco wrote: > 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 Most 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 | removed\_email\_address@domain.invalid Author of “Ruby for Rails” [1] | Ruby/Rails training & consultancy [3] DABlog (DAB’s Weblog) [2] | Co-director, Ruby Central, Inc. [4] [1] [Ruby for Rails](http://www.manning.com/black) | [3] [http://www.rubypowerandlight.com](http://www.rubypowerandlight.com) [2] [http://dablog.rubypal.com](http://dablog.rubypal.com) | [4] [http://www.rubycentral.org](http://www.rubycentral.org)