1

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?
9
  • Virtual Columns were introduced in 11g, not 12c. Commented Aug 19, 2020 at 18:47
  • I wonder if the solution of a materialized view that worked well for my question might be used for yours.. not the same problem but a versatile solution dba.stackexchange.com/q/24280/2453
    – kevinskio
    Commented Aug 19, 2020 at 19:05
  • 1
    You should really try out your solution before posting - especially when you have a complete one like here. Your CREATE TABLE syntax for MEAL has two errors: one is that you miss the FOREIGN KEY key words (as in ` CONSTRAINT fk_truck_meal FOREIGN KEY (truck_id) REFERENCES food_truck(truck_id),` The other is that the truck table is called FOOD_TRUCK, not TRUCK. Which this changes, the MEAL table creates. Commented Aug 20, 2020 at 10:03
  • 1
    However, there is no real way to check whether this works, without some example data. You should now populate your tables with example data and try out your scenarios. In particular "A provider cannot be deleted if any meals attributed to that providers exists" should work: that is the default operating mode for foreign keys. Commented Aug 20, 2020 at 10:06
  • 1
    Performance: "In terms of sizes, the providers number in the hundreds each and the meals number in the hundreds of thousands." That is a very small volume of data. I see no reason for the queries you indicate not to work efficiently. Then again those queries ARE already executed agains the existing data model, right ? The foreign keys make no difference here: they just enforce referential integrity. Their presence plays no role in query performance: queries will not go any faster or any slower. Commented Aug 20, 2020 at 10:10

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.