外键指向多个表的数据库模式困境（独占弧）
希望我的描述比标题好一点，但基本上我对新应用程序模式的一部分有问题，并且我坚持什么是表结构中最易于管理和优雅的解决方案。
仅显示相关字段的基本表结构如下：
航空公司（ID、名称、...）
酒店（ID、名称、...）
供应商（ID，名称，...）
事件（ID，名称，...）
eventComponent (id,name) {例如餐饮、房间租赁、音频/视频...}
eventFlight（id、eventid、airlineid、...）
eventHotel（id、eventid、hotelid、...）
eventSupplier（id、eventid、supplierid、hotelid、eventcomponentid、...）
因此航空公司，酒店，供应商都是参考表，并且事件是通过这些参考表之间的一对多关系创建的。例如，一个事件可能有 2 个航班条目、3 个其他组件条目和 2 个酒店条目。但问题是，在 EventSupplier 表中，供应商可以是供应商，也可以是现有的酒店。因此，当用户在前端构建新事件后，我需要以一种不会让稍后返回此数据成为噩梦的方式存储它。
我已经阅读了大量有关多态关系和独占弧的文章，我认为我的场景肯定更符合独占弧关系。
我在想：
创建表事件供应商（
id 串行主键，
eventid INT NOT NULL,
hotelid INT,
供应商ID INT，
CONSTRAINT UNIQUE (eventid、hotelid、supplierid), -- UNIQUE 允许 NULL
约束检查（hotelid 不为空或供应商id 不为空），
外键（hotelid）参考酒店（id），
外键（供应商 ID）参考供应商（ID）
）；
然后，为了检索该数据，只需使用两个表的外部联接来确定链接的是哪一个。
选择 e.id 作为 eventid，coalesce(h.name,s.name) 作为供应商
来自事件供应商 es
左外连接
s.id = es.supplierid 上的供应商 s
左外连接
h.id 上的 h 酒店 = es.hotelid
其中 h.id 不为 null 或 s.id 不为 null
我的其他选择是在 eventSupplier 表中使用单个外键，并使用另一个“类型”字段，这似乎是检索数据的更困难的解决方案，尽管它如果我想在不进行模式更改的情况下扩展它，那么看起来确实非常灵活。或者直接将 hotelid 存储在供应商表中，然后将一些供应商声明为“酒店”，尽管随后存在我不想要的冗余数据。
任何对此的想法将不胜感激！
干杯 菲尔
Hopefully my description is a little better than the title, but basically I'm having an issue with one part of a new application schema and i'm stuck on what is the most manageable and elegant solution in table structure.
Bare bones table structure with only relevant fields showing would be as follows:
airline (id, name, ...)
hotel (id, name, ...)
supplier (id, name, ...)
event (id, name,...)
eventComponent (id,name) {e.g Food Catering, Room Hire, Audio/Visual...}
eventFlight (id, eventid, airlineid, ...)
eventHotel (id, eventid, hotelid, ...)
eventSupplier (id, eventid, supplierid, hotelid, eventcomponentid, ...)
So airline, hotel, supplier are all reference tables, and an Event is create with 1 to many relationships between these reference tables. E.g an Event may have 2 flight entries, 3 Other components entries, and 2 hotel entries. But the issue is that in the EventSupplier table the supplier can be either a Supplier or an existing Hotel. So after the user has built their new event on the front-end i need to store this in a fashion that doesn't make it a nightmare to then return this data later.
I've been doing a lot of reading on Polymorphic relations and exclusive arcs and I think my scenario is definitely more along the lines or an Exclusive Arc relationship.
I was thinking:
CREATE TABLE eventSupplier (
id SERIAL PRIMARY KEY,
eventid INT NOT NULL,
hotelid INT,
supplierid INT,
CONSTRAINT UNIQUE (eventid, hotelid, supplierid), -- UNIQUE permits NULLs
CONSTRAINT CHECK (hotelid IS NOT NULL OR supplierid IS NOT NULL),
FOREIGN KEY (hotelid) REFERENCES hotel(id),
FOREIGN KEY (supplierid) REFERENCES supplier(id)
);
And then for the retrieval of this data just use an outer join to both tables to work out which one is linked.
select e.id as eventid, coalesce(h.name,s.name) as supplier
from eventSupplier es
left outer join
supplier s on s.id = es.supplierid
left outer join
hotel h on h.id = es.hotelid
where h.id is not null OR s.id is not null
My other options were to have a single foreign key in the eventSupplier table with another field for the "type" which seems to be a harder solution to retrieve data from, though it does seem quite flexible if I want to extend this down the track without making schema changes. Or alternately to store the hotelid in the Supplier table direct and just declare some suppliers as being a "hotel" though there were then be redundant data which I don't want.
Any thoughts on this would be much appreciated!
Cheers
Phil
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如何逐一处理事件并使用
EventGroup将它们分组在一起？编辑：
我只是重命名了实体以适应最新的评论。这是我所能达到的最接近的结果——诚然，我没有正确理解这个问题。
How about handling events one-by-one and using an
EventGroupto group them together?EDIT:
I have simply renamed entities to fit the latest comments. This as close as I can get to this -- admittedly I do not understand the problem properly.
测试解决方案的一个好方法是考虑如果航空公司成为供应商会发生什么。您的解决方案是否可以处理该问题或开始变得复杂。
如果您不需要其他类型供应商的该级别数据，为什么您明确需要沿着供应商路线查找酒店数据？我建议供应商就是供应商，无论是否是出于这些目的的酒店。
如果您想将供应商标记为酒店，则只需将 hotelid 放在供应商表中，或者等待并稍后通过您用来获取其他供应商详细信息的任何机制挂钩该供应商。
A good way to test your solution is to think about what would happen if an airline became a supplier. Does your solution handle that or start to get complicated.
Why do you explicitly need to find hotel data down the supplier route if you don't need that level of data other types of supplier? I would suggest that a supplier is a supplier, whether its a hotel or not for these purposes.
If you want to flag a supplier as a hotel, then simply put hotelid on the supplier table or else wait and hook in the supplier later via whatever mechanism you use to get detail on other suppliers.