EAV数据库方案 [英] EAV Database Scheme

问题描述

我有一个数据库有更多的100K记录。
很多类别和许多项目（每个类别有不同的属性）
一切都存储在EAV中。

I have a db with more that 100K records. A lot of categories and many items (with different properties per category) Everything is stored in a EAV.

如果我试图打破这个计划并为任何类别创建一个唯一的表
是我必须避免的东西？

If I try to break this scheme and create for any category a unique table is something that will I have to avoid?

是的，我知道可能我会有很多表格，我需要改变它们
如果我想添加一个额外的字段，但是是这样错了吗？

Yes, I know that probably I'll have a lot of tables and I'll need to ALTER them if I want to add an extra field, BUT is this so wrong?

我也读了，表格我有，数据库将填充更多的文件
，这对任何文件系统都不好。

I have also read that as many tables I have, the db will be populate with more files and this isn't good for any filesystem.

有什么建议吗？

推荐答案

作为数据库设计中的主要结构，结构将随着数据增长而失败。您知道数据库模式不适合业务模型的方式是，当您需要查询它来报告。 EAV需要许多解决方法和非本机数据库功能，以获得合理的报告。也就是说，即使是最小的查询，也不断创建交叉表/枢轴查询。所有的处理，采取EAV，并把它在一个可查询的格式咀嚼通过CPU周期，是高度容易出错。此外，数据的大小在几何学上增长。如果有10个属性，标准设计中的10行将生成100个EAV行。 100标准行等于1000 EAV行，以此类推。

As the primary structure in a database design, the structure will fail as the data grows. The way you know that a database schema does not fit the business model is when you need to query against it for reporting. EAV's require many workarounds and non-native database functionality in order to get reasonable reports. I.e., you are constantly creating crosstabs/pivot queries for even the smallest query. All that processing to take the EAV and put it in a queryable format chews through CPU cycles and is highly prone to error. In addition, the size of the data is growing geometrically. If you have 10 attributes, 10 rows in a standard design will generate 100 EAV rows. 100 standard rows would equate to 1000 EAV rows and so on.

数据库管理系统旨在处理大量表，这不应该是一个担心。

Database management systems are designed to handle lots of tables and this should not be a worry.

可能创建一个混合解决方案，其中EAV结构是解决方案的部分。但是，规则必须是不能包含查询 [AttributeCol] ='Attribute'。也就是说，您不能过滤，排序，限制任何属性的范围。您不能将特定属性放置在报表的任何位置。它只是一个数据块。结合用于系统其余部分的良好模式，具有存储数据的Blob的EAV可能是有用的。实现这项工作的关键是在自己和开发人员之间执行，不要跨越对属性进行过滤或排序。一旦你走下黑暗的道路，永远将控制你的命运。

It is possible to create a hybrid solution where an EAV structure is part of the solution. However, the rule must be that you can never include a query [AttributeCol] = 'Attribute'. I.e., you can never filter on, sort on, restrict the range on any attribute. You cannot place a specific attribute anywhere in a report or on-screeen. It is just a blob of data. Combined with a good schema for the rest of the system, having an EAV that stores a blob of data can be useful. The key to making this work is enforcement amongst yourself and the developers never to cross the line of filtering or sorting on an attribute. Once you go down the dark path, forever will it dominate your destiny.

这篇关于EAV数据库方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！