Multi-Table Data Validation

• julian.fletcher

  SSCrazy

  Points: 2951

  More actions

  March 9, 2018 at 2:54 am

  #403165

  Problem
  I'm working on a well-established system which is based on a SQL database. (I don't want to bore you with all the exact details.)

  One of our tables is effectively a settings table - it has various flags to dictate how some parts of the system work. Many are bit fields which control whether a particular option has been enabled in the database.

  For example, one setting is HasIntermittentDelivery. It controls which values may be used for a field (PaymentModelActualsOptionID) in one of the tables (T_ContractPaymentMethod) as follows:

  HasIntermittentDelivery = 0
  PaymentModelActualsOptionID may be 1, 3 or 4

  HasIntermittentDelivery = 1
  PaymentModelActualsOptionID may be 1, 2, 3 or 4

  This is currently implemented via the front end by not presenting option 2 to the user if the database has HasIntermittentDelivery = 0.

  However, there's currently nothing to stop invalid data from making its way in to the database by other, nefarious means.

  Solution
  We currently implement this sort of data validation at the database by using views. In this case, it would be:
  Select CPM.IdentityCol Dummy
 From dbo.T_ContractPaymentMethod CPM
 Cross Join dbo.TSys_Setting S
 Cross Join dbo.TSys_Tally_Two
 Where (CPM.PaymentModelActualsOptionID = 2)
  And (S.HasIntermittentDelivery = 0)


  What this is doing is returning rows which violate our rule (i.e. T_ContractPaymentMethod.PaymentModelActualsOptionID can't be 2 if TSys_Setting.HasIntermittentDelivery is 0). The TSys_Tally_Two table simply has two columns so cross joining to it doubles-up the result set. This means that if we put a unique index on the view's column ("Dummy") it will be violated should invalid data be entered, either by

  1.Attempting to add a row to T_ContractPaymentMethod with a value of 2 for PaymentModelActualsOptionID if the setting is not enabled.
  2.Or changing TSys_Setting's HasIntermittentDelivery from 1 to 0 if there are any rows in T_ContractPaymentMethod with PaymentModelActualsOptionID = 2. (The other side of the coin as it were.)

  Question
  Is there a better way to do this? Triggers are an obvious option, but that would mean replicating the validation logic in two places; a trigger on T_ContractPaymentMethod and a trigger on TSys_Setting.

  Thanks.