我应该使用字符串表来提高数据库效率吗？ [英] Should I use a string table to make database more efficient?

问题描述

假设您有一个包含单个表的数据库，例如...

Let's say you have a database with a single table like...

---------------------------------------------
| Name    |  FavoriteFood                   |
---------------------------------------------
| Alice   | Pizza                           |
| Mark    | Sushi                           |
| Jack    | Pizza                           |
---------------------------------------------

拥有一个额外的名为 Strings的表来存储字符串并进行更改会更节省空间FavoriteFood列到字符串表中的索引。在上面的示例中，比萨饼看起来像被存储了两次，但是对于其他表，它似乎只被存储了一次。当然，请假设有1,000,000行和1,000个唯一的字符串，而不是3行和2个唯一的字符串。

Would it be more space-efficient to have an additional table called "Strings" that stores strings, and change the FavoriteFood column to an index in the string table. In the above example, "Pizza" looks like it is stored twice, but with the additional table, it would appear to be stored only once. Of course, please assume there are 1,000,000 rows and 1,000 unique strings instead of just 3 rows and 2 unique strings.

编辑：我们不知道FavoriteFoods之前是什么：它们是用户提供的。字符串表的编程接口将类似于...

We don't know what the FavoriteFoods are beforehand: they are user-supplied. The programmatic interface to the string table would be something like...

String GetString(int ID) { return String at with Row-ID == ID }

int GetID(String s) {
  if s exists, return row-id;
  else {
    Create new row;
    return new row id;
  }
}

所以字符串表似乎更有效，但是现代数据库已经在后台执行了此操作，所以我可以执行简单的单表方法并提高效率？

So the string-table seems more efficient, but do modern databases already do that in the background, so I can just do the simple one table approach and be efficient?

推荐答案

您应该应该根据问题域而不是效率来考虑什么才是一个好的设计（除非您期望拥有数千万以上的行）。

You should be thinking in terms of what makes a good design in terms of your problem domain rather than efficiency (unless you expect to have tens of millions+ rows).

一个设计良好的数据库应采用3NF（第三标准格式）。仅当通过测量确定性能问题后，才能进行反规范化。

A well designed database should be in 3NF (third normal form). Only denormalise when you have identified a performance problem by measuring.

这篇关于我应该使用字符串表来提高数据库效率吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！