php – “水平”与“垂直”表设计,SQL

道歉如果这已经被彻底地覆盖了 – 我已经看到一些相关的帖子,但没有找到任何令我满意的具体情况.

我最近看过一个比较简单的游戏,大约有10k个玩家.在游戏中,您可以捕捉和养殖具有某些属性(即翅膀,角,鬃毛)的宠物.数据库中目前有一个表格如下所示：

-------------------------------------------------------------------------------
| pet_id | wings1 | wings1_hex | wings2 | wings2_hex | horns1 | horns1_hex | ...
-------------------------------------------------------------------------------
|      1 |      1 |     ffffff |   NULL |       NULL |      2 |     000000 | ...
|      2 |   NULL |       NULL |   NULL |       NULL |   NULL |       NULL | ...
|      3 |      2 |     ff0000 |      1 |     ffffff |      3 |     00ff00 | ...
|      4 |   NULL |       NULL |   NULL |       NULL |      1 |     0000ff | ...
etc...

表格就像这样,目前有100列,但一般来说,一只宠物只有大约1-8个属性.每1-2个月添加一个新属性,这需要添加表列.表很少更新和阅读频繁.

我一直在建议我们转向更垂直的设计方案,以获得更好的灵活性,因为我们希望在将来开始增加更多的属性,例如：

----------------------------------------------------------------
| pet_id | attribute_id | attribute_color | attribute_position |
----------------------------------------------------------------
|      1 |            1 |          ffffff |                  1 |  
|      1 |            3 |          000000 |                  2 |  
|      3 |            2 |          ffffff |                  1 |  
|      3 |            1 |          ff0000 |                  2 |  
|      3 |            3 |          00ff00 |                  3 |  
|      4 |            3 |          0000ff |                  1 | 
etc...

旧的开发人员已经引起了人们的关注,因为用户经常搜索具有特定属性的宠物(即必须具有这些属性,必须至少有一个在该颜色或位置上必须具有> 30个属性),这将产生性能问题.目前搜索是非常快的,因为没有JOINS需要,但引入一个垂直表可能意味着每个搜索的属性的附加连接,也将三行的行数.

我的问题的第一部分是如果有人对此有任何建议？我对数据库设计或优化没有特别的经验.

我已经为各种案例进行了测试,但是它们的确很大程度上没有结果 – 对于我所运行的所有查询(即半秒到20秒),时间差异很大,所以我想我的第二部分问题是,在PHP中使用微时间(true)是否有更可靠的查询时间方法.

谢谢.

引用那些认为是errors not to make之一的人：

So what are the benefits that are touted for EAV? Well,there are none. Since EAV tables will contain any kind of data,we have to PIVOT the data to a tabular representation,with appropriate columns,in order to make it useful. In many cases,there is middleware or client-side software that does this behind the scenes,thereby providing the illusion to the user that they are dealing with well-designed data.

EAV models have a host of problems.

Firstly,the massive amount of data is,in itself,essentially unmanageable.

Secondly,there is no possible way to define the necessary constraints — any potential check constraints will have to include extensive hard-coding for appropriate attribute names. Since a single column holds all possible values,the datatype is usually VARCHAR(n).

Thirdly,don’t even think about having any useful foreign keys.

Finally,there is the complexity and awkwardness of queries. Some folks consider it a benefit to be able to jam a variety of data into a single table when necessary — they call it “scalable”. In reality,since EAV mixes up data with metadata,it is lot more difficult to manipulate data even for simple requirements.

The solution to the EAV nightmare is simple: Analyze and research the users’ needs and identify the data requirements up-front. A relational database maintains the integrity and consistency of data. It is virtually impossible to make a case for designing such a database without well-defined requirements. Period.

The table goes on like that and currently has 100+ columns,but in general a single pet will only have around 1-8 of these attributes.

这看起来像一个归一化的情况：将表分解成多个,例如一个用于喇叭,一个用于机翼,所有这些都通过外键连接到主实体表.但是请确保每个属性仍然映射到一个或多个列,以便您可以定义约束,数据类型,索引等.

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!