How to design database request/approval tables?

Question

I have the following kind of request tables:

oversea_study_request
course_request
leave_request

in these request functions, approving officer can post multiple remarks and also approve or reject the request. The system must be able to capture the history of the actions taken.

What is the best way to design it?

Should I create a common table to store the approval information and remarks.
Should I store in each request table the approval information and remarks instead.

Can someone advise on the pros and cons of each approach?

Are remarks and approvals uniform across all request kinds? Also, can remarks be entered _before_ the request itself is approved? Can they be entered _after_? What information is associated to each remark - do they contain timestamps and when you say "history" do you refer to these timestamp? Anything else you can tell us about "history"? Do you need to have a predefined list of approval "steps" (as in document management or PDM systems)? Also, which DBMS are you using and do you intend to leverage [clustering](http://use-the-index-luke.com/sql/clustering/index-organized-clustered-index)? — Branko Dimitrijevic, Aug 23 '12 at 13:13

score 4 · Answer 1 · edited May 23 '17 at 12:22

Similar to the fields organisation question here: How to better organise database to account for changing status in users; and my answer there:

If the all the requests have the same fields, field types and info, including mandatory (NOT NULL) and optional, etc. then it's better to put all the requests into one requests table. Designate one field to be request_type, with an int for efficiency and SQL convenience, or an ENUM type. Example:

overseas study = 1
course = 2
leave = 3

Similarly, do that for the approvals table also...if the process is the same for each type then store those together. Store the request id (requests.id). Since you have multiple approval-comments and approval+rejection possible, store these in approvals.action and approvals.action_date. If "actions" are independent of "approve/reject" - that is, if you can post a comment without approving/rejecting OR if you can approve/reject without a comment - then store the actions and comments separately, and include the request.id.

So you have:

Table1: requests
    id INT
    request_type INT (or ENUM)
    request_date DATETIME
    ...

Table2: approvals (or 'actions', to be general)
    id
    request_id    # (refers to requests.id above)
    action_type   # (approve or reject)
    action_date
    comment

If comments and approvals are NOT necessarily together, then:

Table2: actions
    id, request_id, action_type, action_date

Table3: comments
    id, request_id, comment, comment_date

And of course, add the user_id, username, etc. tables/fields. (The id in each table is it's own Primary Key)

Each request + actions + comments can be found with a SELECT and LEFT JOINs

Btw, it's "overseas" study, not "oversea" study - it's not a course in an airplane ;-)

How to design database request/approval tables?

1 Answers1