I like pure relational design but sometimes there is a need for an Entity/Value method of storing data. Especially where the user needs to frequently create a new type of data.
我喜欢纯粹的关系设计,但有时需要一种存储数据的实体/值方法。特别是在用户需要频繁创建新类型数据的情况下。
I have seen in some commercial software that they dynamically create standard tables rather than use EV tables.
我在一些商业软件中看到他们动态创建标准表而不是使用EV表。
Obviously this isn’t a solve-all solution and can work only if you limit the sorts of data you can define. However, it has the following benefits:-
显然,这不是一个解决所有的解决方案,只有在限制您可以定义的各种数据时才能工作。但是,它有以下好处: -
- The model is clearly defined - you don’t have to interrogate the schema to understand the schema.
- You get better performance as tables hold only the data for that type of data, hence no obviously unrelated rows need to be queried.
- Similarly, Indexes only hold values for the type of data that the table defines, again improving performance. (i.e. in the EV model, if the index is valueid,entityid - you will be searching values for entities that have nothing to do with your query - unless you partition the index).
模型已明确定义 - 您无需询问架构即可理解架构。
您可以获得更好的性能,因为表只包含该类型数据的数据,因此不需要查询明显不相关的行。
同样,索引仅保存表定义的数据类型的值,从而再次提高性能。 (即在EV模型中,如果索引是valueid,则为entityid - 您将搜索与查询无关的实体的值 - 除非您对索引进行分区)。
So to me this sounds great, but run the idea past a DBA and you get a lot of sucking of teeth. Is this a good idea, what are the pitfalls, and has anyone tried to do this?
所以对我来说这听起来很棒,但是这个想法超过了DBA,你会得到很多吸吮牙齿。这是一个好主意,有什么陷阱,有没有人试图这样做?
2 个解决方案
#1
First of all: application should not be able to alter database structure, because they souldn't have the privileges to do so.
首先:应用程序不应该能够改变数据库结构,因为它们没有权限这样做。
Second: I think the overhead of creating a good solution for creating a good database structure on the fly wouldn't pay off.
第二:我认为为动态创建良好的数据库结构创建一个好的解决方案的开销不会带来回报。
Third: It could cause some serious side effects with other things using the database (backups and maintaining scripts, other applications, etc.) when done improperly.
第三:如果不正确地使用数据库(备份和维护脚本,其他应用程序等),可能会导致其他一些严重的副作用。
#2
Not too long ago, I built an online survey application. I used a hybrid form as database schema:
不久前,我建立了一个在线调查应用程序。我使用混合形式作为数据库模式:
- Store the values in a key/value table initially.
- Use dynamic, temporary tables in for they key/value pairs are transformed into a custom table per survey.
- Filling these temporary tables could be done with a single, heavy
INSERT INTO temp_table SELECT ...statement, using a lot ofLEFT JOINs.
最初将值存储在键/值表中。
使用动态临时表,因为它们的键/值对每次调查都会转换为自定义表。
使用大量LEFT JOIN可以使用单个繁重的INSERT INTO temp_table SELECT ...语句来填充这些临时表。
This approach was ideal for this particular application, because a survey usually has a phase in which the answers are inserted, and a phase in which the results are analyzed.
这种方法对于这种特定的应用是理想的,因为调查通常具有插入答案的阶段,以及分析结果的阶段。
- Building the temporary table could be time-consuming at times, but only had to be done once after the survey was closed.
- Querying the results table afterwards was very quick.
- Temporary tables where automatically recreated if they were deleted or out of date. They did not have to be backed up because of this.
建立临时表有时可能很耗时,但只需在调查结束后进行一次。
之后查询结果表非常快。
临时表,如果删除或过期则自动重新创建。由于这个原因,他们不需要备份。
So my advise: think about what the intended use is. This example only worked out because of the specific requirements of this application. Are you mainly inserting and updating values (OLTP)? Or do you want to run aggregate queries (OLAP)? Build your schema according to the answer on this question.
所以我建议:考虑一下预期的用途。此示例仅在此应用程序的特定要求下才得以解决。您主要是插入和更新值(OLTP)吗?或者您想运行聚合查询(OLAP)?根据此问题的答案构建您的架构。
#1
First of all: application should not be able to alter database structure, because they souldn't have the privileges to do so.
首先:应用程序不应该能够改变数据库结构,因为它们没有权限这样做。
Second: I think the overhead of creating a good solution for creating a good database structure on the fly wouldn't pay off.
第二:我认为为动态创建良好的数据库结构创建一个好的解决方案的开销不会带来回报。
Third: It could cause some serious side effects with other things using the database (backups and maintaining scripts, other applications, etc.) when done improperly.
第三:如果不正确地使用数据库(备份和维护脚本,其他应用程序等),可能会导致其他一些严重的副作用。
#2
Not too long ago, I built an online survey application. I used a hybrid form as database schema:
不久前,我建立了一个在线调查应用程序。我使用混合形式作为数据库模式:
- Store the values in a key/value table initially.
- Use dynamic, temporary tables in for they key/value pairs are transformed into a custom table per survey.
- Filling these temporary tables could be done with a single, heavy
INSERT INTO temp_table SELECT ...statement, using a lot ofLEFT JOINs.
最初将值存储在键/值表中。
使用动态临时表,因为它们的键/值对每次调查都会转换为自定义表。
使用大量LEFT JOIN可以使用单个繁重的INSERT INTO temp_table SELECT ...语句来填充这些临时表。
This approach was ideal for this particular application, because a survey usually has a phase in which the answers are inserted, and a phase in which the results are analyzed.
这种方法对于这种特定的应用是理想的,因为调查通常具有插入答案的阶段,以及分析结果的阶段。
- Building the temporary table could be time-consuming at times, but only had to be done once after the survey was closed.
- Querying the results table afterwards was very quick.
- Temporary tables where automatically recreated if they were deleted or out of date. They did not have to be backed up because of this.
建立临时表有时可能很耗时,但只需在调查结束后进行一次。
之后查询结果表非常快。
临时表,如果删除或过期则自动重新创建。由于这个原因,他们不需要备份。
So my advise: think about what the intended use is. This example only worked out because of the specific requirements of this application. Are you mainly inserting and updating values (OLTP)? Or do you want to run aggregate queries (OLAP)? Build your schema according to the answer on this question.
所以我建议:考虑一下预期的用途。此示例仅在此应用程序的特定要求下才得以解决。您主要是插入和更新值(OLTP)吗?或者您想运行聚合查询(OLAP)?根据此问题的答案构建您的架构。