面向列的数据库VS排面向数据库 [英] Column oriented database vs row oriented database

问题描述

我用排面向数据库的设计时间长，除了数据仓库项目和大数据样本，我还没有使用面向列的数据库设计用于OLTP应用程序。

I have used row oriented database design for long time and except for datawarehouse projects and Big data samples, I have not used column oriented database design for OLTP app.

我行面向表看起来像

ID, Make, Model, Month, Miles, Cost
1   BMW   Z3     12     12000  100

有些人在我们的团队崇尚面向列的数据库设计。
他们认为，所有的列名应在属性表属性名称。
然后另一个表报价将有两列属性名和为PropertyValue

Some people in our team advocating column oriented database design. They suggest that all the column names should be property names in a Property table. Then another table Quote will have two columns PropertyName and PropertyValue.

在.NET code，我们读到每个键和比较，并转换为强类型的对象。在code真的是越来越乱了。

In the .net code, we read each key and compare and convert to strongly typed object. The code is really getting messy.

if (qwi.DomainCode == typeof(CoreBO.Base.iQQConstants.MBPCollateralInfo).Name)
     {
        if (qwi.RefCode == iQQConstants.MBPCollateralInfo.ENGINETYPE)
        {
           Aspiration = qwi.Value;
        }
        else if (qwi.RefCode == iQQConstants.MBPCollateralInfo.FUELTYPE)
        {
           FuelType = qwi.Value;
        }
        else if (qwi.RefCode == iQQConstants.MBPCollateralInfo.MAKE)
        {
           Make = qwi.Value;
        }
        else if (qwi.RefCode == iQQConstants.MBPCollateralInfo.MILEAGE)
        {
           int reading = 0;
           bool success = int.TryParse(qwi.Value, out reading);
           if (success)
           {
              OdometerReading = reading;
           }
}
}

此面向列的设计实际参数的是，我们不会有改变表模式和存储过程（我们仍然使用存储过程而不是实体框架）。

The arguement for this column oriented design is that we won't have to change table schema and the stored proc(we are still using stored proc instead of Entity Framework).

好像我们正在进入真正的问题。被列导向的设计以及在行业内所接受。

Seems like we are heading into real problem. Is Column oriented design well accepted in the industry.

推荐答案

我有你的术语的麻烦。你所描述的EAV结构（代表实体 - 属性 - 值）。

I am having trouble with your terminology. You are describing an EAV structure (standing for Entity-Attribute-Value).

旁白：A面向列的数据库通常是指从别人分开存储每列的数据库（当我了解了数据库，这被称为垂直分区，但我不认为这抓获）。例子包括西沙群岛和Vertica的。

Aside: A "column-oriented" database usually refers to a database that stores each column separately from others (when I learned about databases, this was called "vertical partitioning", but I don't think that caught on). Examples include Paracel and Vertica.

这是实体 - 属性 - 值数据库存储每个属性为一个实体作为一个单独的行。

An entity-attribute-value database is storing each attribute for an entity as a separate row.

这是你与你的特定结构的第一个问题是打字。一些属性是字符串和一些数字。这将成为在世界EAV管理噩梦。要么你存储的一切作为字符串（输给输入校验值，并保证算术单词的能力），也包括针对不同类型与类型的列多列（进行查询要复杂得多）。

The first problem that you have with your particular structure is typing. Some of the attributes are strings and some are numbers. This becomes a management nightmare in an EAV world. Either you store everything as strings (losing the ability to type check values and to guarantee that arithmetic words) or you include multiple columns for different types with a type column (making queries much more complicated).

同样，约束和外键引用更难实现。此外，因为你重复实体ID和属性ID上的每一行，该数据往往占用了更多的空间。 NULL 值通常比较节省空间。

Similarly, constraints and foreign key references are much harder to implement. Also, because you are repeating the entity id and attribute id on each row, the data often takes up more space. NULL values are typically quite space efficient.

在OLTP方面，你有另外一个问题。当你要插入一个实体，通常要插入一堆属性为好。现在一个插入已经变成了许多插入，你要开始交易，这些包裹，影响性能。

On the OLTP side, you have another problem. When you want to insert an entity, you typically want to insert a bunch of attributes as well. One insert has now turned into many inserts, and you'll want to start wrapping these in transactions, affecting performance.

考虑到所有这些缺点，你可能会想的从不的使用EAV模型。有他们的地方。他们是特别有用的属性随时间变化。再说，如果你有一个应用程序，用户可以把标签与自己的信息。在这种情况下，混合的方法是最好的解决办法。使用普通的关系表与公共信息的列。使用EAV表为每个实体可选信息。

Given all these shortcomings, you might think never use EAV models. There is a place for them. They are particularly useful when attributes are changing over time. Say, if you have an application where users can put in their own information with tags. In such cases, a hybrid approach is the best solution. Use a regular relational table with many columns for the common information. Use an EAV table for optional information for each entity.

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