Is it ever OK to denormalize a foreign key relation?

I have the following (simplified) MySQL schema:

companies
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

users
+----------------------+--------------+------+-----+---------+----------------+
| Field                | Type         | Null | Key | Default | Extra          |
+----------------------+--------------+------+-----+---------+----------------+
| id                   | int(11)      | NO   | PRI | NULL    | auto_increment |
| email                | varchar(255) | NO   |     |         |                |
| username             | varchar(255) | NO   | UNI | NULL    |                |
| company_id           | int(11)      | YES  |     | NULL    |                |
| first_name           | varchar(255) | YES  |     | NULL    |                |
| last_name            | varchar(255) | YES  |     | NULL    |                |
| title                | varchar(255) | YES  |     | NULL    |                |
+----------------------+--------------+------+-----+---------+----------------+

notes
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id         | int(11)      | YES  |     | NULL    |                |
| title           | varchar(255) | YES  |     | NULL    |                |
| body            | text         | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+

A company has many users,开发者_运维问答 and a user has many notes.

My question is, is it bad to denormalize a little by adding a company_id foreign key to the notes table? The reason is that then I can get all the notes for a company with one fewer join than in the current schema.

Or to put the question another way: What are the pros and cons of adding a redundant company_id foreign key to notes?

---

Don't do it. MySQL is very smart about joins. What seems like a lot of work in theory often ends up being done very efficiently. Use explain if you aren't sure about how much work a query will generate.

To save on typing up a join is a very poor reason to denormalize a data base.

---

Disadvantages of de-normalizing (adding the company_id to the notes table):

• The data can get out of sync
• It uses a little bit more disk space

Advantages:

• Joins are simpler. Sometimes this is a good reason, for example: you could normalize an address table by moving the city name to a 'cities' table. But I wouldn't do it, because it will complicate a lot of queries, plus it will complicate inserts and updates, and there is little benefit. There is a way to reduce this 'added complexity' problem somewhat: using a view.
• For some cases it might be faster (those cases are rare).
• For very large databases, it might be easier to shard the data.