title: Oracle - referential integrity with multiple types of data
body: <p>I'm working on a set of database tables in Oracle and trying to figure out a way to enforce referential integrity with slightly polymorphic data.</p>
  
  <p>Specifically, I have a bunch of different tables--hypothetically, let's say I have Apples, Bananas, Oranges, Tangerines, Grapes, and a hundred more types of fruit. Now I'm trying to make a table which describes performing steps involving a fruit. So I want to insert one row that says "eat Apple ID 100", then another row which says "peel Banana ID 250", then another row which says "refrigerate Tangerine ID 500", and so on. </p>
  
  <p>Historically, we've done this in two ways:</p>
  
  <p>1 - Include a column for each possible type of fruit. Use a check constraint to ensure that all but one column is NULL. Use foreign keys to ensure referential integrity to our fruit. So in my hypothetical example, we'd have a table with columns <code>ACTION, APPLEID, BANANAID, ORANGEID, TANGERINEID</code>, and <code>GRAPEID</code>. For the first action, we'd have a row <code>'Eat', 100, NULL, NULL, NULL, NULL, NULL</code>. For the second action, we'd have <code>'Peel', NULL, 250, NULL, NULL, NULL</code>. etc. etc. </p>
  
  <p>This approach is great for getting all of Oracle's RI benefits automatically, but it just doesn't scale to a hundred types of fruit. You end up getting too many columns to be practical. Just figuring out which type of fruit you are dealing with becomes a challenge.</p>
  
  <p>2 - Include a column with the name of the fruit, and a column with a fruit ID. This works also, but there isn't any way (AFAIK) to have Oracle enforce the validity of the data in any way. So our columns would be <code>ACTION, FRUITTYPE</code>, and <code>FRUITID</code>. The row data would be <code>'Eat', 'Apple', 100</code>, then <code>'Peel', 'Banana', 250</code>, etc. But there's nothing preventing someone from deleting Apple ID 100, or inserting a step saying <code>'Eat', 'Apple', 90000000</code> even though we don't have an Apple with that ID.</p>
  
  <p>Is there a way to avoid maintaining a separate column per each individual fruit type, but still preserve most the benefits of foreign keys? (Or technically, I could be convinced to use a hundred columns if I can hide the complexity with a neat trick somehow. It just has to look sane in day-to-day use.)</p>
  
  <p><strong>CLARIFICATION:</strong> 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. If we had one table which contained each of these unrelated things, I wouldn't have a problem, but it would also be a really weird design.</p>