Using virtual columns to implement an exclusive arc relationship

At the risk of bringing up an often discussed question, I wanted to get some feedback on using virtual columns (specifically Oracle 12c+) to implement an exclusive arc relationship. The database exists and has been in use for 25+ years and the goal is to improve referential integrity without major schema changes (major, unfortunately, is a subjective term).

Consider the following entities: RESTAURANT, FOOD_TRUCK, WALKING_VENDOR, and MEAL. The following rules apply:

• A meal can be provided by any one of the three providers (restaurant, food_truck, or walking_vendor).
• A provider cannot be deleted if any meals attributed to that providers exists
• A provider is not required to provide a meal
• Meals can be deleted

In terms of sizes, the providers number in the hundreds each and the meals number in the hundreds of thousands. The queries generally fall into the following categories:

• Select all meals that were provided by specific provider in specified date range
• Select all meals in specified date range with left joins to the providers
• Select all meals from a provider
• Select the provider given a meal

In the current schema, the relationship is modeled as

CREATE TABLE restaurant (
  restaurant_id NUMBER,
  ... restaurant related attributes ...
  CONSTRAINT pk_restaurant PRIMARY KEY(restaurant_id)
);

CREATE TABLE food_truck (
  truck_id NUMBER,
  ... food_truck related attributes ...
  CONSTRAINT pk_food_truck PRIMARY KEY(truck_id)
);

CREATE TABLE walking_vendor (
  walking_id NUMBER,
  ... walking_vendor attributes ...
  CONSTRAINT pk_walking_vendor PRIMARY KEY(walking_id)
);

CREATE TABLE meal (
  meal_id       NUMBER,
  ... meal related attributes ...
  provider_type VARCHAR2(1),
  provider_id   NUMBER,
  CONSTRAINT pk_meal PRIMARY KEY(meal_id),
  CONSTRAINT chk_provider_type CHECK (provider_type IN ('R', 'T', 'W'))
);

Note that there is no referential integrity on the meal table to the providers, which I’m trying to fix. When I look at some of the solutions presented here, they have various shortcomings (e.g. not enforcing an exclusive provider relationship, not preventing the deletion of a provider if meals are still attributable to them). The answer in this post was interesting, but it does not prevent the deletion of a provider, which would orphan one or more meals.

One clean sheet approach would be to create a PROVIDER table and have the sub-types be detail tables; however, there are two shortcomings. First, it would be considered a major change to the schema and, second, a row in a provider detail table should not be deleted if a meal is attributed to it.

The only solution that seems to work is to have a column in the meal table for each of the provider types. Prior to Oracle 12c, that would have also involved a check constraint to enforce the exclusive relationship. The major cons to this solution are the code (schema?) smell of having NULL values for the unused relationships and the modest increase in storage. With the introduction of virtual columns, the meal table could be defined as

CREATE TABLE meal (
  meal_id       NUMBER,
  ... meal related attributes ...
  provider_type VARCHAR2(1),
  provider_id   NUMBER,
  restaurant_id NUMBER GENERATED ALWAYS AS (DECODE(provider_type, 'R', provider_id, NULL)),
  truck_id      NUMBER GENERATED ALWAYS AS (DECODE(provider_type, 'T', provider_id, NULL)),
  walking_id    NUMBER GENERATED ALWAYS AS (DECODE(provider_type, 'W', provider_id, NULL)),
  CONSTRAINT pk_meal PRIMARY KEY(meal_id),
  CONSTRAINT chk_provider_type CHECK (provider_type IN ('R', 'T', 'W')),
  CONSTRAINT fk_restaurant_meal FOREIGN KEY (restaurant_id) REFERENCES restaurant(restaurant_id),
  CONSTRAINT fk_truck_meal FOREIGN KEY (truck_id) REFERENCES food_truck(truck_id),
  CONSTRAINT fk_walking_meal FOREIGN KEY (walking_id) REFERENCES walking_vendor(walking_id)
);

A similar solution was presented in an AskTOM post (in my case the providers are not a detail of a meal, thus no FK back to meal exists in the provider tables). I tested this approach at a small scale and it seems to work well and it appears to meet the goal of "minor" changes.

My questions are:

• Does anyone have experience with this approach?
• What are the drawbacks?
• Any suggestions on alternative solutions?