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:
1 Asked on December 19, 2020 by revolucion-for-monica
1 Asked on December 19, 2020 by aditya-bhardwaj
1 Asked on December 19, 2020
availability groups distributed availability groups sql server
1 Asked on December 17, 2020 by captainahab
4 Asked on December 16, 2020 by ronaldo
1 Asked on December 16, 2020 by j-hache
1 Asked on December 16, 2020 by simonell
1 Asked on December 16, 2020 by gagandeep-singh
1 Asked on December 15, 2020
1 Asked on December 14, 2020 by adam-mulla
1 Asked on December 13, 2020 by mike_butak
0 Asked on December 12, 2020
1 Asked on December 12, 2020 by michelle
3 Asked on December 11, 2020 by rai-micheal
1 Asked on December 10, 2020 by mihir-rane
0 Asked on December 8, 2020 by mouchin777
1 Asked on December 7, 2020 by chad-richardson
0 Asked on December 7, 2020 by nalzok
3 Asked on December 7, 2020 by genichm
1 Asked on December 7, 2020 by misterghost
Get help from others!
© 2023 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP