Jelle Druyts

I'm modelling the database for the WeFly247.NET project, and a small issue came up so I thought I'd share the problem and proposed solution with you... Ideally, because you're way smarter and know of a much better way to do this of course

It basically has to do with inheritance hierarchies in a relational database, and I found a pretty good introduction to mapping objects to tables on the Object Architects site but I'll just limit myself to the problem at hand here.

Imagine a SeatServiceRequests table in which all passenger requests on a flight are stored. The list of things you can request is in the SeatServiceItems table; these include a range of drinks, books, papers, pillows, blankets, "special attention" by the flight attendant , ... and belong to certain categories (Drinks, Reading Material, Comfort, ...). In another use case, you can schedule a meal to be delivered to you and choose a drink to go with that. So the MealRequests table needs a link to the SeatServiceItems - but here's the tricky part: only drinks are allowed here (logically). You can't drink a newspaper with your meal (at least not if we can stop you from trying).

There are options to enforce the constraint that a requested SeatServiceItem must be a drink of course, such as checking the item's category in the business logic, or using a trigger to check the constraint when a new row is added in the MealRequests table. However, that makes it an 'implicit' constraint: it's not modelled in the ERD and not really enforced by the database itself:

So that doesn't make me very happy...

The problem, in fact, is that we have sort of an inheritance relationship between a SeatServiceItem and a Drink - a Drink is a SeatServiceItem - but I didn't want to make the database too complex and go about one of the full object-to-table mapping schemes here.

One thought was to create a "Drinks" view over the SeatServiceItems table so that only items from the "Drinks" category are shown. That way we can link the MealRequests table to the Drinks view. Unfortunately, SQL Server doesn't support relationships between tables and views so we can't use that to model the relationship or to enforce the constraint.

So I took this up with a colleague who proposed a pretty simple solution for this (thanks, doggi ). Just add an extra "Drinks" table containing the primary keys of the SeatServiceItems that are in fact drinks (so basically this is an emulated view on the SeatServiceItems). Now you can establish all relationships without problems.

It's not as elegant as I'd want it to be (when working with drinks, you have 2 tables to maintain) but it beats the 'implicit' constraint idea so I guess I'll be using this solution. What do you think? Any issues with this, any other ideas?