基于帖子类型的数据库结构
我一直想知道处理不同字段的帖子类型的最佳方法是什么。消息的帖子类型(标题、正文)与照片的字段(标题、url 路径、描述)等具有不同的字段。
理想情况下,我希望将其放在一个帖子表下,因为这样更容易在视图中聚合。它可以使用 jQuery 显示和隐藏字段,但这会在数据库中留下多余的字段。
关于解决这个问题的最佳方法有什么建议吗?
谢谢
I've been wondering what the best way to approach having post types with different fields. A post type of a message (title, body) would have different fields to that of a photo (title, url path, description) etc.
Ideally I would like to have it under one table Posts as it's then easier to aggregate in a view. It could work showing and hiding fields with jQuery but that would leave redundant fields in the db.
Any suggestions on the best way of going about this?
Thanks
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
我建议
因此,对于适用于所有帖子类型的字段,它们存储在通用帖子表中。
然后,您可以使用帖子类型 ID 来查找其他字段,并根据帖子类型 ID 检索这些字段的数据。
所以你的通用帖子表看起来像
|编号 |标题 |作者 ID |文字|
您发布的类型表将是
|编号 |帖子类型 | <-- post_type 是一个字符串
假设您有一个需要日期字段的“日历”帖子类型。我们将其称为照片帖子类型。该表将被称为“calendar_post_fields”,帖子类型将是“calendar”。
|编号 |帖子 ID |日期 |
I'd recommend
So for the fields that apply to all posts types, they're stored in the generic posts table.
You can then use the post type id to locate the additional fields, and to retrieve data for those fields based on the post type id.
So your generic post table would look like
| id | title | author_id | text |
You post type table would be
| id | post_type | <-- post_type is a string
And let's say you have a "calendar" post type that needs a date field. We'll call it photo post type. The table would be called "calendar_post_fields", and the post type would be "calendar".
| id | post_id | date |
首先要事。您是否能够在运行时添加新的“类型”?这个问题的答案可能会使其他可靠的解决方案无效。
如果问题的答案是“否”,那么就采用庞大的解决方案。为公共字段建立一个基表,为特定字段建立一个单独的表。特定表具有基表的外键。这种方式避免了动态sql,避免了冗余字段,并且给出了良好的查询性能。
还有其他解决方案,但我不想提及它们,除非上述解决方案不能满足您的需求。
First things first. Do you have to ability to add new "types" at runtime? The answer to this question could invalidate otherwise solid solutions.
If the answer to the question is "no", then go with gargantuan's solution. Have a base table for the common fields, and a separate table for the specific fields. The specific tables have a foreign key to the base table. This way avoids dynamic sql, avoids redundant fields, and gives good query performance.
There are other solutions but I would rather not mention them unless the above solution does not fulfill your need.