Always completely normalize your datamodel?

Rails
1

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

2

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

3

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

4

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 :slight_smile: 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 | [email protected]
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 | [3] http://www.rubypowerandlight.com
[2] http://dablog.rubypal.com | [4] http://www.rubycentral.org