一个表列用于许多fk表？ [英] One table column for many fk tables?
问题描述
这种情况的最佳解决方案/实践是什么？
我有一个表，可以引用多个表（对象）？
What is the best solution/practice for situation like this? I have a table, that can reference to multiple tables (objects)?
这里是一个表UserCalendar的例子。它是一个表，用户保存他的事件，但也从后面的系统插入这个表。用户执行一些服务，它们有截止日期，并且这些服务也插入在此表中。问题是没有这样的表UserEvent表。用户应将此日历中的所有活动保存为说明。我应该尽可能简化。
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 |说明| ObjectType | ObjectId
UserCalendar
UserCalendarId | UserId | Description | ObjectType | ObjectId
使用这个选项我不需要FK这个表。我只能更改ObjectType（通知，服务，日历），并使用该表的ID作为ObjectId。在事件的情况下将没有这样的表，并且这将是空字段。
我们称之为伪FK列。
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）
或者我可以使用理论上的每个FK的多个表。 p>
2) Or I could use as says in theory with multiple tables for each FK.
UserCalendar
UserCalendarId | UserId |说明
UserEvent
UserCalendarId | EventId
UserServices
UserCalendarId | ServiceId
UserNotifications
UserCalendarId | NotificationId
...
UserCalendar
UserCalendarId | UserId | Description
UserEvent
UserCalendarId |EventId
UserServices
UserCalendarId|ServiceId
UserNotifications
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.
推荐答案
两种设计：
UserCalendar
UserCalendarId | UserId |描述
UserCalendar
UserCalendarId | UserId | Description
UserCalendarAttributes
UserCalendarId | ObjectType | ObjectId
UserCalendarAttributes
UserCalendarId | ObjectType | ObjectId
这样，您可以随意添加任何额外的引用到您的日历条目（例如event 通知），而且您在 UserCalendar 表格与任何其他表格之间没有紧耦合。
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.
如果将FK放入主要的 UserCalendar 表格中，您需要多长时间访问一次附加数据（事件，服务等）。
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.
这篇关于一个表列用于许多fk表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
