一个表列可用于多个 fk 表？
对于这种情况，最好的解决方案/做法是什么？ 我有一个表，可以引用多个表（对象）？
以下是 UserCalendar 表的示例。这是一个用户保存事件的表，但系统也从后面插入到该表中。用户执行一些有截止日期的服务，这些服务也被插入到该表中。问题是没有 UserEvent 表这样的表。用户应将其所有事件保存在此日历中作为描述。我应该让这件事尽可能简单。
我可以用两种方式设计。
1）
用户日历
用户日历 ID |用户 ID |描述 |对象类型 |对象ID
使用此选项我不必 FK 这个表。我只能更改 ObjectType（通知、服务、日历）并使用该表的 id 作为 ObjectId。如果是事件，则不会有这样的表，并且该字段将为空字段。 我们称此为伪 FK 列。
2） 或者我可以按照理论上的说法为每个 FK 使用多个表。
用户日历
用户日历 ID |用户 ID |描述
用户事件
用户日历 ID |事件 ID
用户服务
UserCalendarId|ServiceId
用户通知
UserCalendarId |NotificationId
...
对于属于特定类型的每个系统事件或任何其他自定义事件，该外部关系表可以是数字
第一个解决方案是快速实现。
What is the best solution/practice for situation like this?
I have a table, that can reference to multiple tables (objects)?
Here is an example for a table UserCalendar. It's a table where user saves his event, but also system inserts in this table from behind. User executes some services, which have deadlines and those are inserted in this table also. Problem is that there is no such a table as UserEvent table. User should save all his events in this calendar as description. I should make this simple as possible.
I can design this two way.
1)
UserCalendar
UserCalendarId | UserId | Description | ObjectType | ObjectId
Using this option I would not have to FK this tables. I could only change ObjectType (Notification,Service,Calendar) and use id of that Table as ObjectId. In case of Event there will be no such a Table and this would be null field.
We an call this pseudo FK column.
2)
Or I could use as says in theory with multiple tables for each FK.
UserCalendar
UserCalendarId | UserId | DescriptionUserEvent
UserCalendarId |EventIdUserServices
UserCalendarId|ServiceIdUserNotifications
UserCalendarId |NotificationId
...
This foreign relationships tables can be a n number, for each system event or any other custom event that belongs to certain type
First solution is a fast implementation.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我更喜欢第三种解决方案作为两种设计的混合：
这种方式，您可以根据需要随意添加对日历条目的附加引用（例如事件和通知），并且 UserCalendar 表和任何其他表。
如果将 FK 放入您的主 UserCalendar 表中有意义，它还取决于您访问附加数据（事件、服务等）的频率。
我建议更多地追求灵活性，而不是性能，尽管这会稍微增加架构的复杂性。功能需求发生变化的可能性比您遇到性能问题的可能性更大。
I'd prefer a 3rd solution as a mix of your two designs:
This way you are free to add as many additional references to your calendar entry as you wish (e. g. event and notification) and you don't have a tight coupling between the UserCalendar table and any other tables.
It also depends a little bit on how often you have to access that additional data (events, service, etc.) if it makes sense to put the FK in your main UserCalendar table.
I suggest going for flexibility more than for performance, though you will slightly increase the complexity of your schema. It's more likely that feature requirements will change than you will be hit by performance problems.