Databases
StackOverflow2013
Postsdbo
POSQL table referencing multiple foreign keys, while defining order and guaranteeing referential integrity
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL table referencing multiple foreign keys, while defining order and guaranteeing referential integrity
    text
    copied!<p>My customer has specified, that he needs different actions to be executed, when certain conditions in an internal logic are met. Each action type (so far <code>Command</code> and <code>WriteVariable</code>) has an individual set of specific information, and therefore needs to be stored in an individual table. The user must be able to define the order in which the actions are to be executed.</p> <p>I have the following setup for a Database:</p> <pre><code>LogicTable * OutputID * Description OutputTable &lt;== a pure relational table * OutputID * LogicID * ActionID &lt;== this references one of the action tables (Command/WriteVariable) * ActionTypeID * Sequence ActionTypeTable * ActionTypeID * Description CommandTable * CommandID &lt;== corresponds to ActionID in OutputTable three columns with further command-specific information WriteVariableTable * WriteVariableID &lt;== corresponds to ActionID in OutputTable four columns with further write-variable-specific information </code></pre> <p>My problem is that I cannot have multiple relational tables, because I cannot guarantee the sequence of the actions across multiple tables. I cannot have multiple columns with foreign-keys to each individual action in the output table (customer requirement). With the above setup I cannot have referential integrity, causing possible <code>ConfigurationException</code> in my application due to foreign-key entries w/out corresponding primary key entry.</p> <p>Is there a design that will enable referential integrity and manage to guarantee the order of referenced actions?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload