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:
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:
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:
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP