How can a ‘Request’ store a ‘Result’ code and its associated ‘Error’ code and still enforce the relationship between Result and Error?

I’m an application developer creating the database structure to represent a flat file message format. I’d like to ask the collective knowledge the best/correct way to represent the following scenario:

Request table (PK: RequestID) contains requests; a request has a Result property which is indeterminate (null) until the request has completed.

Result table (PK ResultID) is a lookup table containing (currently) two possible results:

  • Success (Result ID = 0)
  • Failure (Result ID = 1)

ErrorCode table (PK ErrorCodeID) is a lookup table containing error details and their parent ResultID:

  • No Error (ErrorCodeID = 0, ResultID = 0)
  • Generic Error (ErrorCodeID = 1, ResultID = 1)
  • Queue Full (ErrorCodeID = 2, ResultID = 1)
  • Unsupported Interface (ErrorCodeID = 3, ResultID = 1)
  • etc…

I’ve created a one to many relationship between Result (one) and ErrorCode (many). A ‘Success’ Result can only have a ‘No Error’ Error Code, while a ‘Failure’ Result can have a single error code of ‘Generic Error’, ‘Queue Full’, ‘Unsupported Interface’, etc.

When the Request has completed, I need to store the result and its associated error code.

I’ve thought of combining the two tables but that strikes me as repeating columns.

I’ve also thought of having the Request table store the ResultID and the ErrorCodeID but this doesn’t enforce the Result to ErrorCode relationship.

I’m a big believer in database that ‘defends itself’ from bad data so I want the relationship to reject a Result/ErrorCode combination that is invalid; a.k.a. a Result of ‘Success’ and an ErrorCode of ‘Generic Error’ or a Result of ‘Failure’ and an ErrorCode of ‘No Error’.

I’m also a big believer in solid initial design so when changes come down the pike at a later date (as they always do) the structure will not need rework.

Thank you in advance for your time.

Regards, John E.