Using virtual columns to implement an exclusive arc relationship

Database Administrators Asked by Jim D on October 31, 2020

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)

  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

  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?

Add your own answers!

Related Questions

How to insert several csv files into Elasticsearch?

1  Asked on December 19, 2020 by revolucion-for-monica


passing variable values to procedure in postgresql via psql

1  Asked on December 19, 2020 by aditya-bhardwaj


PostgreSQL: interpolate missing value

1  Asked on December 17, 2020 by captainahab


duplicate key error index MongoDB

1  Asked on December 16, 2020 by simonell


Why is Postgres service failing?

1  Asked on December 13, 2020 by mike_butak


MySQL Shell dump utility – maximum possible chunk size?

1  Asked on December 10, 2020 by mihir-rane


Ask a Question

Get help from others!

© 2023 All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP