It's not clear to me why you need to identify the FRUIT_TYPE on the TASKS table. On the face of it that's just a poor (de-normalised) data model.
In my experience, the best way of modelling this sort of data is with a super-type for the generic thing (FRUIT in your example) and sub-types for the specifics (APPLE, GRAPE, BANANA). This allows us to store common attributes in one place while recording the particular attributes for each instance.
Here is the super-type table:
create table fruits
(fruit_id number not null
, fruit_type varchar2(10) not null
, constraint fruit_pk primary key (fruit_id)
, constraint fruit_uk unique (fruit_id, fruit_type)
, constraint fruit_ck check (fruit_type in ('GRAPE', 'APPLE', 'BANANA'))
)
/
FRUITS has a primary key and a compound unique key. We need the primary key for use in foreign key constraints, because compound keys are a pain in the neck. Except when they are not, which is the situation with these sub-type tables. Here we use the unique key as the reference, because by constraining the value of FRUIT_TYPE in the sub-type we can guarantee that records in the GRAPES table map to FRUITS records of type 'GRAPE', etc.
create table grapes
(fruit_id number not null
, fruit_type varchar2(10) not null default 'GRAPE'
, seedless_yn not null char(1) default 'Y'
, colour varchar2(5) not null
, constraint grape_pk primary key (fruit_id)
, constraint grape_ck check (fruit_type = 'GRAPE')
, constraint grape_fruit_fk foreign key (fruit_id, fruit_type)
references fruit (fruit_id, fruit_type)
, constraint grape_flg_ck check (seedless_yn in ('Y', 'N'))
)
/
create table apples
(fruit_id number not null
, fruit_type varchar2(10) not null
, apple_type varchar2(10) not null default 'APPLE'
, constraint apple_pk primary key (fruit_id)
, constraint apple_ck check (fruit_type = 'APPLE')
, constraint apple_fruit_fk foreign key (fruit_id, fruit_type)
references fruit (fruit_id, fruit_type)
, constraint apple_type_ck check (apple_type in ('EATING', 'COOKING', 'CIDER'))
)
/
create table bananas
(fruit_id number not null
, fruit_type varchar2(10) not null default 'BANANA'
, constraint banana_pk primary key (fruit_id)
, constraint banana_ck check (fruit_type = 'BANANA')
, constraint banana_fruit_fk foreign key (fruit_id, fruit_type)
references fruit (fruit_id, fruit_type)
)
/
In 11g we can make FRUIT_TYPE a virtual column for the sub-type and do away with the check constraint.
So, now we need a table for task types ('Peel', 'Refrigerate', 'Eat ', etc).
create table task_types
(task_code varchar2(4) not null
, task_descr varchar2(40) not null
, constraint task_type_pk primary key (task_code)
)
/
And the actual TASKS table is a simple intersection between FRUITS and TASK_TYPES.
create table tasks
(task_code varchar2(4) not null
, fruit_id number not null
, constraint task_pk primary key (task_code, fruit_id)
, constraint task_task_fk ask foreign key (task_code)
references task_types (task_code)
, constraint task_fruit_fk foreign key (fruit_id)
references fruit (fruit_id)
/
If this does not satisfy your needs please edit your question to include more information.
"... if you want different tasks for different fruits..."
Yes I wondered whether that was the motivation underlying the OP's posted design. But usually workflow is a lot more difficult than that: some tasks will apply to all fruits, some will only apply to (say) fruits which come in bunches, others will only be relevant to bananas.
"In our actual logic, the 'fruits' are totally disparate tables with
very little commonality. Think customers, employees, meetings, rooms,
buildings, asset tags, etc. The list of steps is supposed to be
free-form and allow users to specify actions on any of these things."
So you have a bunch of existing tables. You want to be able to assign records from these tables to tasks in a freewheeling style yet be able to guarantee the identify of the specific record which owns the task.
I think you still need a generic table to hold an ID for the actor in the task, but you will need to link it to the other tables somehow. Here is how I might approach it:
Soem sample existing tables:
create table customers
(cust_id number not null
, cname varchar2(100) not null
, constraint cust_pk primary key (fruit_id)
)
/
create table employees
(emp_no number not null
, ename varchar2(30) not null
, constraint emp_pk primary key (fruit_id)
)
/
A generic table to hold actors:
create table actors
(actor_id number not null
, constraint actor_pk primary key (actor_id)
)
/
Now, you need intersection tables to associate your existing tables with the new one:
create table cust_actors
(cust_id number not null
, actor_id number not null
, constraint cust_actor_pk primary key (cust_id, actor_id)
, constraint cust_actor_cust_fk foreign key (cust_id)
references customers (cust_id)
, constraint cust_actor_actor_fk foreign key (actor_id)
references actors (actor_id)
)
/
create table emp_actors
(emp_no number not null
, actor_id number not null
, constraint emp_actor_pk primary key (emp_no, actor_id)
, constraint emp_actor_emp_fk foreign key (emp_no)
references eployees (emp_no)
, constraint cust_actor_actor_fk foreign key (actor_id)
references actors (actor_id)
)
/
The TASKS table is rather unsurprising, given what's gone before:
create table tasks
(task_code varchar2(4) not null
, actor_id number not null
, constraint task_pk primary key (task_code, actor_id)
, constraint task_task_fk ask foreign key (task_code)
references task_types (task_code)
, constraint task_actor_fk foreign key (actor_id)
references actors (actor_id)
/
I agree all those intersection tables look like a lot of overhead but there isn't any other way to enforce foreign key constraints. The additional snag is creating ACTORS and CUSTOMER_ACTORS records every time you create a record in CUSTOMERS. Ditto for deletions. The only good news is that you can generate all the code you need.
Is this solution better than a table with one hundred optional foreign keys? Perhaps not: it's a matter of taste. But I like it better than having no foreign keys at all. If there is on euniversal truth in database practice it is this: databases which rely on application code to enforce relational integrity are databases riddled with children referencing the wrong parent or referencing no parent at all.
