Is there a rule of thumb for including the value of a foreign key in a table to avoid large joins?

Question

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?

Answer 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.

Answer 2

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.

Answer 3

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.