如何处理“扩展”桌子
我正在设计一个涉及“页面”的应用程序，其中有许多“块”，其中有多种类型的块。然而，我正在努力寻找一种在数据库中保存这些数据的好方法。我想有一种流行的方法可以做到这一点，我正在考虑以下两种方法：
方法一
- 页面表
- ID
- 用户
- 创建、更新等。
- 块
- ID
- 页面（Blocks.page = Pages.id）
- 创建、更新
- block_type（例如“文本”）
- block_id（Blocks.block_id = nBlocks.id，其中 n = block_type）
- 文本块
- ID
- 特定于文本的属性
这样做的优点是所有类型的块所共有的属性（创建、更新、页面、标题、顺序）都保存在一个地方。您也不必查询每个块类型表来检查当前页面的块，因为您将拥有此“索引”。缺点是在查找块时可能会变得有点混乱，但这仅取决于正确实现它（查找页面的所有块，按块类型分组，对每个块类型运行查询）。
像以前一样接近两个
- 页面
- 文本块
- ID
- 页面
- 正文、特定于文本的项目
- 创建、更新、订购
- 、顺序列表块
- ID
- 页面
- 列出特定项目
- 创建、更新、订购等。
这样做的优点是消除了查找正确表来查询每个块的混乱方式。缺点是块顺序无法轻松管理（更新任何其他块中的顺序！= $order），并且每个表必须具有相同的创建、更新等字段，这使得如果需要改变的话，需要付出一些努力。更大的问题是，必须针对每个页面查询每个特定于块的表，而不仅仅是明确具有该页面的块的块表。
还有第三种更好的方法吗？我认为最好的方法是第一种（它至少比第二种更规范化，并且表逻辑并不令人困惑），但我想知道是否有我遗漏的东西： ）
I'm designing an application involving "pages", which have many "blocks", of which there are several types of blocks. I'm struggling to find a good way to hold this data in the database however. I'd imagine there is a popular way of doing this, here are two I am considering:
Approach One
- Pages table
- id
- user
- created, updated etc.
- Blocks
- id
- page (Blocks.page = Pages.id)
- created, updated
- block_type (e.g. "text")
- block_id (Blocks.block_id = nBlocks.id where n = block_type)
- Text Blocks
- id
- text-specific attributes
The pros of this are that attributes that are common to all types of block (created, updated, page, title, order) are kept in one place. You would also not have to query each block-type table to check for blocks for the current page as you would have this "index". Cons are that it could become a little confusing in finding blocks but this just depends on implementing it correctly (find all blocks for page, group by block_type, run queries on each block_type).
Approach two
- Pages as before
- Text Blocks
- id
- page
- body, text-specific items
- created, updated, order
- List Blocks
- id
- page
- list-specific items
- created, updated, order etc.
Pros of this are removing the confusing way of finding the correct table to query for each block. Cons are that block-order can't be easily managed (update where order in any other block != $order) and that each table must have the same created, updated etc. fields which makes for a bit of effort if they need to be changed. The bigger issue is that each block-specific table must be queried for every page, rather than just block tables which definitely have blocks for the page.
Is there a third, better way of doing this? I think the best approach is the first (it's more normalised than the second at least and the table-logic isn't that confusing) but I'd like to know if there's something I'm missing :)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
方法二有一个巨大的缺点：顺序。
我会采取方法一，或者这样：
（我知道它看起来很脏，但它有效）
您可以重用某些列来优化它
优点： 与方法一
缺点：
额外提示
如果您计划使用 VisualStudio 创建应用程序，请使用实体数据模型（.edmx 文件），“按照应有的方式”创建具有继承性的实体，然后单击“从模型生成数据库”。我相信这两种方法都有优点和缺点。
Approach Two has a huge con: the ordering.
I'd take Approach One, or this:
(i know it looks dirty, but it works)
u can optimize it reusing some columns
Pros: same as Approach One
Cons:
Extra Tip
in case you are planning to create the application with VisualStudio, use an Entity Data Model (.edmx file), create the entities with inheritance "the way it should be" and then click on Generate database from model. i believe that has all the pros and non of the cons of both of your approaches.