这是一个“正确的”数据库设计？ [英] Is this a "correct" database design?

问题描述

我正在使用新版本的第三方应用程式。在这个版本中，数据库结构被改变，他们说提高性能。

I'm working with the new version of a third party application. In this version, the database structure is changed, they say "to improve performance".

旧版本的数据库有一个通用的结构：

The old version of the DB had a general structure like this:

TABLE ENTITY
(
    ENTITY_ID,
    STANDARD_PROPERTY_1,
    STANDARD_PROPERTY_2,
    STANDARD_PROPERTY_3,
    ...
)

TABLE ENTITY_PROPERTIES
(
    ENTITY_ID,
    PROPERTY_KEY,
    PROPERTY_VALUE
)

所以我们有一个主表，其中包含基本属性的字段，还有一个单独的表来管理添加的自定义属性

so we had a main table with fields for the basic properties and a separate table to manage custom properties added by user.

新版本的数据库具有如下结构：

The new version of the DB insted has a structure like this:

TABLE ENTITY
(
    ENTITY_ID,
    STANDARD_PROPERTY_1,
    STANDARD_PROPERTY_2,
    STANDARD_PROPERTY_3,
    ...
)

TABLE ENTITY_PROPERTIES_n
(
    ENTITY_ID_n,
    CUSTOM_PROPERTY_1,
    CUSTOM_PROPERTY_2,
    CUSTOM_PROPERTY_3,
    ...
)

所以，现在当用户添加一个自定义属性时，一个新列被添加到当前 ENTITY_PROPERTY 表，直到达到最大列数（由应用程序管理），然后创建一个新表。

So, now when the user add a custom property, a new column is added to the current ENTITY_PROPERTY table until the max number of columns (managed by application) is reached, then a new table is created.

所以，我的问题是：这是一个设计DB结构的正确方法吗？这是提高效果的唯一方法吗？旧的结构需要许多连接或子选择，但这个结构在我看来不是很聪明（甚至是正确的）...

So, my question is: Is this a correct way to design a DB structure? Is this the only way to "increase performances"? The old structure required many join or sub-select, but this structute don't seems to me very smart (or even correct)...

推荐答案

我已经看到这样做在假设（通常未经证实）加入的费用 - 它基本上是一个行重的数据表成一个列重表。他们遇到了自己的限制，因为你暗示，当他们用完了列。创建新的表。

I have seen this done before on the assumed (often unproven) "expense" of joining - it is basically turning a row-heavy data table into a column-heavy table. They ran into their own limitation, as you imply, by creating new tables when they run out of columns.

我完全不同意。

我个人认为，我会坚持旧的结构，并重新评估性能问题。这不是说旧的方式是正确的方式，它只是略微优于我的意见中的改进，而不需要对数据库表和DAL代码进行大规模的重新设计。

Personally, I would stick with the old structure and re-evaluate the performance issues. That isn't to say the old way is the correct way, it is just marginally better than the "improvement" in my opinion, and removes the need to do large scale re-engineering of database tables and DAL code.

这些表对我很大程度上是静态的... 缓存将是一个更好的性能改进，不破坏数据库，我会先看看。做一次昂贵的提取一次，并把它粘在内存中的某个地方，然后忘记你的麻烦（注意，我在说明需要管理缓存，但静态数据是最容易管理的之一）。

These tables strike me as largely static... caching would be an even better performance improvement without mutilating the database and one I would look at doing first. Do the "expensive" fetch once and stick it in memory somewhere, then forget about your troubles (note, I am making light of the need to manage the Cache, but static data is one of the easiest to manage).

或者，等待每个数据库达到最大表数的那一天： - ）

Or, wait for the day you run into the maximum number of tables per database :-)

其他人建议完全不同商店。这是一个完全可行的可能性，如果我没有现有的数据库结构，我会考虑它。也就是说，我看不出为什么这个结构不能适应RDBMS。我已经看到它做了几乎所有大规模的应用程序，我已经工作。有趣的是，他们都走了一个类似的路线，并且大多是成功的实施。

Others have suggested completely different stores. This is a perfectly viable possibility and if I didn't have an existing database structure I would be considering it too. That said, I see no reason why this structure can't fit into an RDBMS. I have seen it done on almost all large scale apps I have worked on. Interestingly enough, they all went down a similar route and all were mostly "successful" implementations.

这篇关于这是一个“正确的”数据库设计？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！