我正在尝试构建（现在只是思考/规划/绘图关系:]）小模块化系统来构建基本网站（主要是为了简化我们作为网页设计师日常执行的常见任务）。
我对数据库设计/存储内容的整个想法没有什么困惑。
1.，大多数网站上最痛苦的事情（根据我的经验），是具有准相同布局/骨架的页面，具有不同的信息 - 例如标题、图片和信息集 - 但是，在 cms 中制作特殊模板/特殊模块碰巧比将其编辑为文本花费更多的精力 - 但是，在这里我们失去了一些操作潜力 - 我们无法获得“仅标题”，因为，CMS/系统将整个内容理解为一个文本字段
所以，我想要这两个表格 - 用于保存内容结构信息的表格（例如，照片数量<1;500）:]、标题和内容文本和照片（大）&画廊） - 如何 - 以及另一个表格，其中包含“集合”的所有内容、模块和部分（我对各种结构化信息的工作名称） -
table module_descriptors (HOW)
id int
structure - *???*
table modules (WHAT)
id int
module_type - @link to module_descriptors id
content - *???*
2.，我喜欢的是 - 我不需要很多表格 - 我不需要不像数据库有 6810 个表，每个模块一个，用于它的描述，用于其他。数字与文本的关系，...而且我也不喜欢有 60 列的表格，例如 content_us、content_it、category_id、parent_id。
我想我可以将结构描述和内容本身（注意到 ??? ？）保存为 XML 或 CSV，但也许我正在尝试重新发明轮子，并且对此的答案是隐藏的在一些我没有研究过的设计模式中。
希望我能说得通，并能得到一些回复——给我你的意见、优点、缺点……否则就把我送进地狱。谢谢
编辑：我的问题也是这样：这种方法有意义吗？它是否适合编辑？难道没有更好的东西吗？道德吗？当我这样做时，小猫不会死吗？如果我想读取并比较从数据库中提取的 30 个 XML（例如我想比较某些内容），这对于服务器来说是不是太多了？技术部分 - 如何做 - 只是问题的一部分:)
I'm trying to build (right now just thinking/planning/drawing relations :] ) little modular system to build basic websites (mostly to simplify common tasks we as webdesigners do routinely).
I got little stuck with database design / whole idea of storing content.
1., What is mostly painful on most of websites (from my experience), are pages with quasi same layout/skelet, with different information - e.g. Title, picture, and set of information - but, making special templates / special modules in cms happens to cost more energy than edit it as a text - however, here we lose some operational potential - we can't get "only titles", because, CMS/system understands whole content as one textfield
So, I would like to this two tables - one to hold information what structure the content has (e.g. just variable amount of photos <1;500) :], title & text & photo (large) & gallery) - HOW - and another table with all contents, modules and parts of "collections" (my working name for various structured information) - WHAT
table module_descriptors (HOW)
id int
structure - *???*
table modules (WHAT)
id int
module_type - @link to module_descriptors id
content - *???*
2., What I like about this is - I don't need many tables - I don't like databases with 6810 tables, one for each module, for it's description, for misc. number to text relations, ... and I also don't like tables with 60 columns, like content_us, content_it, category_id, parent_id.
I'm thinking I could hold the structure description and content itself (noted the ??? ?) as either XML or CSV, but maybe I'm trying to reinvent the wheel and answer to this is hidden in some design pattern I haven't looked into.
Hope I make any sense at all and would get some replies - give me your opinion, pros, cons... or send me to hell. Thank you
EDIT: My question is also this: Does this approach make sense? Is it edit-friendly? Isn't there something better? Is it moral? Don't do kittens die when I do this? Isn't it too much for server, If I want to read&compare 30 XMLs pulled from DB (e.g. I want to compare something)? The technical part - how to do it - is just one part of question:)
发布评论
评论（1）
您暗示的设计模式称为序列化LOB。您可以以传统方式（作为列）存储每个条目相同属性的一些数据。对于可变属性，将其格式化为 XML 或 MarkDown 或任何您想要的格式，并将其存储在 TEXT BLOB 中。
当然，您将无法使用 SQL 表达式查询 BLOB 中的各个元素。搜索或排序时需要使用的任何内容都应该位于常规列中。
回复评论：如果您的文本 blob 是 XML 格式，您可以使用 MySQL 5.1 及更高版本支持的 XML 函数。但这无法从索引中受益，因此会导致搜索速度非常慢。
如果您尝试将
LIKE或RLIKE与通配符一起使用，情况也是如此。如果不使用索引，搜索将导致全表扫描。您还可以尝试使用 MySQL FULLTEXT 索引，但这对于搜索 XML 数据来说不是一个好的解决方案，因为它无法区分文本内容与 XML 标记名称和 XML 属性之间的区别。
因此，只需对您想要搜索或排序的任何字段使用常规列即可。这样你会更快乐。
回复问题：如果您的文档确实需要可变结构，那么您几乎没有选择。如果使用得当，SQL 假定每一行都具有相同的结构（即列）。您的替代方案是：
有些人采用称为实体属性值 (EAV) 的反模式来存储可变属性，但老实说，不要这样做。有关这种情况可能会出现多么严重的错误的故事，请阅读这篇文章：坏CaRMa。
The design pattern you're hinting at is called Serialized LOB. You can store some data in the conventional way (as columns) for attributes that are the same for every entry. For attributes that are variable, format them as XML or MarkDown or whatever you want, and store it in a TEXT BLOB.
Of course you lose the ability to use SQL expressions to query individual elements within the BLOB. Anything you need to use in searching or sorting should be in conventional columns.
Re comment: If your text blob is in XML format, you could search it with XML functions supported by MySQL 5.1 and later. But this cannot benefit from an index, so it's going to result in very slow searches.
The same is true if you try to use
LIKEorRLIKEwith wildcards. Without using an index, searches will result in full table-scans.You could also try to use a MySQL FULLTEXT index, but this isn't a good solution for searching XML data, because it won't be able to tell the difference between text content and XML tag names and XML attributes.
So just use conventional columns for any fields you want to search or sort by. You'll be happier that way.
Re question: If your documents really require variable structure, you have few choices. When used properly, SQL assumes that every row has the same structure (that is, columns). Your alternatives are:
Some people resort to an antipattern called Entity-Attribute-Value (EAV) to store variable attributes, but honestly, don't go there. For a story about how bad this can go wrong, read this article: Bad CaRMa.