Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to model the process of where one co-worker enters a statement of their proposed work procedure and another co-worker commenting and approving the statement. Currently I have one table as such:

METHODOLOGY
  methodology_id
  entered_by
  statement
  entered_date
  status_code
  reviewed_by
  reviewer_comments
  reviewed_date

status codes are as such:

1 = in progress by originator
2 = finished and locked by originator
3 = in progress by checker
4 = finished, locked and rejected by checker
5 = finished, locked and approved by checker
6 = approved method was unlocked due to revision to methodology
7 = the originator was changed with a new originator after method was approved
8 = the checker was changed with a new checker 

Should this be broken out as such:

METHODOLOGY_STATEMENT
  methodology_id
  entered_by
  statement
  entered_date
  status_code

METHODOLOGY_REVIEW
  methodology_review_id
  methodology_id
  status_code
  reviewed_by
  reviewer_comments
  reviewed_date

Right now the requirement is only one Review per Method Statement.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

Either way could be correct.

Choose 1 table if you want to make it difficult to change the number of reviews possible. It may be counter-intuitive to want to make something difficult, but if it would actually be a bad idea to make it 1:n, then building in some pain to make the change has some logic.

But, you almost certainly want to go with two tables for long term flexibility. If it was me, I'd put a trigger on there to enforce the 'one review per methodology statement' rule. If that changes, just modify or drop the trigger.

One question to keep in mind, though: if you go to multiple reviews, how would you resolve status codes that conflict? For instance, what if you had one code 4 and one code 5 for the same methodology statement?

UPDATE:

...or you can do the much better thing and use a unique constraint as @Max Vernon suggests.

share|improve this answer
    
Instead of using a trigger, why not simply add a unique constraint to the methodology_id field in the review table? –  Max Vernon Oct 31 '13 at 3:59
    
By the way, I totally agree that you should use a table that can support more than one review per statement. –  Max Vernon Oct 31 '13 at 4:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.