对象和对象元模式设计
我正在开发一个将多种类型的对象保存到数据库中的项目。其中包括：文章、民意调查、作家......等。其中一些在设计时对我来说是未知的，所以我想做的是构建一个通用表模式，它将使我能够将任何类型的项目保存到该数据库中。
我的设计
表对象
objectID int
title varchar
body text
type int #will represent the ID of the type of object am inserting
考虑到情况或要求可能需要额外的数据，我决定这将转到我所说的“对象元数据”，如下所示：
表Object_MetaData
metaID int
metaKey varchar
metaIntKey int #representing an integer value of metaKey which is calculated using some algorithm to speed up queries
metaValue varchar(1000)
我正在做顺便说一句，所有工作都在 PHP 和 MySQL 中进行。
在进行有关数据库长期性能的设计时，我想到了几件事：
1）将 int、boolean、小文本、大文本保存到 metaValue 中是否有效？
2）从长远来看，元数据表将会增长得相当快。这是维护噩梦吗？ MySQL 将如何处理这个问题？
3）在php中，当获取对象时，我必须循环每个对象来获取其元数据，或者使用延迟加载加载所有元数据一次，或者在通过__get魔术方法请求时加载任何单个元键。因此，当获取包含 50 个对象的列表时，使用我建议的任何方法都将执行至少 50 个 select 语句。有没有更好更有效的方法来做到这一点？
欢迎对此设计有任何想法或评论。
Am working on a project that will save many types of objects into the database. These include: articles, polls, writers .. etc. Some of those are unknown to me at design time so what i am trying to do is build a generic table schema that will enable me to save any type of item into this database.
My design
Table Object
objectID int
title varchar
body text
type int #will represent the ID of the type of object am inserting
Having that circumstances or requirements might need additional data, i decided that this will go to what i call "object metadata" as follows:
Table Object_MetaData
metaID int
metaKey varchar
metaIntKey int #representing an integer value of metaKey which is calculated using some algorithm to speed up queries
metaValue varchar(1000)
I am doing all the work in PHP and MySQL btw.
A couple of things came up to my mind when doing this design regarding the performance of the database on the long run:
1) Is saving int, boolean, small text, big text into the metaValue effecient?
2) on the long run the metadata table is going to grow quite quickly. Is this a maintainance nightmare? how is MySQL going to to handle this?
3) IN php when fetching objects, i will have to loop each object to fetch its metadata, or load all metadata once using lazy loading or load any single metakey once its requested via __get magic method. So when fetching a list of say 50 objects, at least 50 select statements will be executed when using any of the methods i suggested. Is there a better more efficient way of doing this?
Any thoughts or comments on this design are welcome.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我建议重新构建您的解决方案。此模式称为“实体-属性-值”(EAV)，通常被视为反模式。相反，在列（同一表或另一个表（如果需要））中定义元数据属性。还要使用正确的数据类型。
我建议提供Bill Karwin的书SQL AntiPatterns 读一读。它有一些很棒的见解，可以专门为您提供帮助。您可以从此演示文稿中感受到其中的一些内容。 。
I'd suggest re-architecting your solution. This pattern is called "Entity-Attribute-Value" (EAV), and is commonly seen as an anti-pattern. Instead, define the metadata attributes in columns (of the same table, or another if needed). Use the proper data types as well.
I'd suggest giving Bill Karwin's book SQL AntiPatterns a read. It has some great insights that will give you a hand here specifically. You can get a feel for some of it from this presentation...