Question

我有:

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    |                |
+-----------------+--------------+------+-----+---------+----------------+

一家公司有许多用户,用户有许多说明。

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?

Answer 1

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.

避免在打字时加入,是失去数据基的极差的原因。

Answer 2

脱热的缺点(涉及公司——见附注表):

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.

友情链接