Database schema dilemma with foreign keys pointing to multiple tables (exclusive arc)

Question

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

Answer 1

How about handling events one-by-one and using an EventGroup to 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.

Answer 2

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.