具有单个默认子项的数据库模型父子项。
是否有一种最佳实践模式来模拟以下父子关系，其中可以将一个子项标记为默认子项。
我可以想到两种方法。哪个更好或者还有其他更好的替代方案？
- ParentTable
- Id
- ChildTable
- Id
- ParentId
- IsDefault
或者
- ParentTable
- Id
- DefaultChildId
- ChildTable
- Id
- ParentId
Is there a best practice pattern to model the following parent child relationship where exactly one child can be flagged as the default child.
I can think of two ways. Which is better or is there another better alternate?
- ParentTable
- Id
- ChildTable
- Id
- ParentId
- IsDefault
or
- ParentTable
- Id
- DefaultChildId
- ChildTable
- Id
- ParentId
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我赞成完全防止无效数据存在的数据模型。如果您使用第二种方法并在 DefaultChildId 和 ChildTable 之间创建外键关系，那么您可以添加约束以确保您的数据库永远不会处于无效状态 - 例如，如果您尝试删除一个子项是某人的默认子项，数据库会阻止您。使用第一种方法，不会。
具体来说（如评论中所述），应在 DefaultChildId 上使用 ON DELETE RESTRICT 约束，并在 ParentID 上使用 ON DELETE CASCADE 约束。这允许更灵活地创建记录（您不需要已有默认子项即可创建父项），防止在未先更新到新默认值的情况下意外删除默认子项，并智能地处理删除父母。
I favor data models that prevent invalid data from existing at all. If you use the second approach and create a foreign key relationship between DefaultChildId and the ChildTable, then you can add constraints to ensure that your database can't ever be in an invalid state -- if you try to, say, delete a child that is somebody's default child, the database will prevent you. Using the first approach, it won't.
Specifically (as noted in a comment), ON DELETE RESTRICT constraint should be used on DefaultChildId and ON DELETE CASCADE on ParentID. This allows creation of records with more flexibility (you don't need to have a default child already there to be able to make a parent), prevents accidental deletion of default child without first updating to a new default, and intelligently handles deletion of the parent.
一般来说，第二个更好，因为它更容易确保一个且只有一个子级是默认子级。
SQL也比较
一点
清晰
Generally speaking the second one is better since its easier to ensure that one and only one child is the default child.
The SQL is also a slightly clearer
Compare
vs