0

Example:

We have a table with 500 rows in table "Cars". There is also a table "Colors" which has roughly 20 rows. Table "Cars" has a foreign key reference to table "Colors". To get the color of the car we would need to inner join on table "Colors":

select car.Name,
color.Name as Color
from dbo.Cars as car
inner join dbo.Colors as color on color.ID = car.ColorID

The result would look something like this:

+-------------+--------+
|    Name     | Color  |
+-------------+--------+
| Rolls Royce | Red    |
+-------------+--------+

But lets say you have 10,000,000 rows in Cars and 2,000,000 rows in Colors; the inner join would cause quite a performance hit. It would probably be better to include the column "Color" and "ColorID" in table "Cars" to avoid this inner join. So you query would look like this:

select Name,
Color
from dbo.Cars

Is there a rule of thumb to rather include the foreign key value inside your table once your table reaches an x-amount of rows? Or would it be better to remove the foreign key completely and just have a column called "Color" in table "Cars" thus causing table "Colors" to be redundant?

  • 2
    It's better to have 2 tables with index and do join than have color in car table.Can you show us the explain of your query with join ? – Seb3W Aug 24 '16 at 9:55
  • 1
    But what if a color consultant decides that Red should be called Burgundy? Would you update all the Cars and other tables dependent of Colors. I think denormalizing is nice on a BI enviroment where you are not updating data. – vercelli Aug 24 '16 at 9:59
  • @Seb3W I am not using this concept in a current application. It was just something that came to mind. Are you suggesting adding a table called dbo.CarsColors with columns CarID and ColorID? – Pierre Nortje Aug 24 '16 at 10:54
  • I am guessing you are using 2,000,000 colors as an example. That is a lot of colors to manage. – datagod Aug 24 '16 at 13:40
1

Don't denormilize, keep 2 tables (cars and colors) whatever tables size. I think that you don't need to select 10 millions with one query but if you realy need it, you can do it by batch. I don't know your problematic but by default, never denormilize.

If you have a problem with big tables you can expose it but i am pretty sure that denormalization will be not the solutiuon.

  • could you maybe enlighten me "why" you shouldn't denormalize? The way I see it is that normalization can be a huge bottleneck when you need to execute select queries than need to join 10 tables to get the correct relation. Denormalizing (or better; adding an extra reference) would save me 9 joins in those cases. I can only see benefits in that. – Flip Vernooij Aug 24 '16 at 12:15
  • 1
    yes you can save 9 join... but imagine your update... – Seb3W Aug 24 '16 at 12:49
  • 1
    Denormalisation usually introduces the possibility of data to become inconsistent both by actually making it possible and making updates more work (so there being more room for bugs) - so you don't want to do it in your application databases which are your sources of truth. It is often done in reporting databases (often called data warehouses) to speed certain families of report. Inconsistent/corrupt data here is still a problem but at least it can be easily rebuilt from the application DBs when a problem is found. – David Spillett Aug 24 '16 at 13:05
  • @Seb3W How often do you update the id of a record? And aslong as you use foreign keys, even that should just work. I understand there are cases where extra id references might cause issues,.. but should I always run 10 joins because there are some special cases where I maybe have to update 10 tables? I know this use-case you are completely right.. but stating "never denormalize" has proven me wrong. – Flip Vernooij Aug 24 '16 at 13:24
1

But it is not a large join
ID is just an integer

color.ID should be the PK - that is a very fast join - even on a very large table

Red is a short color and it takes more space than the integer PK / FK.
By de-normalizing you would make a large table even larger.
The index on Color would be larger.

The rule of thumb is not to de-normalize large tables.

0

In an OLTP system, they should be kept as two separate tables with appropriate indexing on the ID columns.

In a data warehouse star schema design it would be perfectly legitimate to denormalise Color into the Car dimension. I am assuming here that Car would be a dimension, supprting facts such as Journey or CarSale etc.

The denormalisation would be handled by your ETL process during import to the warehouse from your OLTP system. This would usually be done via intermediate staging tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.