数据库设计：EAV还是不EAV？ [英] Database Design: to EAV or not to EAV?

问题描述

说我有一个实体，它将有很多属性，一些我知道现在和其他人将是用户定义。

1）我有一个主表，并将其与辅助名称 - 值对表相关联吗？所有属性都在辅助EAV表中。

• 或 -

2）我在主表中放入最常见的属性（不是所有用户都需要它们，所以我期望有很多NULL条目），并且具有用户定义属性的辅助EAV表？ / p>

• 或 -

$ p

解决方案通常，很多空单元格都很便宜，不值得归一化。回到＃2的唯一缺点是，如果你有非常多的行（百万 - 性能问题可能出现），非常大的列数（大于约20 - 只是讨厌的数据）或者在EAV表上有一些唯一的约束。

这样说，现在是2011，并且使用一个带有数据库抽象层的编程框架是有意义的这些天，使您不是直接设计数据库关系。像Django的对象关系映射器允许你专注于模型本身并让最佳实践照顾自己（95％的时间）。此教程将帮助您开始使用。 Django仅适用于Web开发数据库建模。对于非网络环境，其他框架会更好。

Say I have an entity that will have many attributes, some I know about now and others will be user defined. What's the best way to model this?

1) Do I have a main table and relate it to a secondary name-value pair table? All the attributes go in the secondary EAV table.

• OR -

2) Do I put the most common attributes (not all users will need them, so I expect a lot of NULL entries) in the main table and have the secondary EAV table for the user defined attributes?

• OR -

3) Some other approach I have not thought of?

解决方案

Typically, lots of empty cells are cheap and not worth normalizing away. The only draw back to #2 is if you have a very large number of rows (millions - where performance problems could arise), a very large number of columns (more than about 20 - where it's just annoying to look at the data), or there are a number of unique constraints on the EAV table.

With that said, it is now 2011 and it makes sense to use a programming framework with a database abstraction layer these days so that you're not designing database relationships directly. Something like Django's Object Relational Mapper allow you to focus on the models themselves and let best practices take care of themselves (95% of the time). This tutorial will help you get started. Django only applies to web development database modeling. For non-web environments, other frameworks will be better.

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