StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POJava web application & database design
text
Body
copied!<p>I have developed a work flow web application in Java with an Oracle backend. The workflow requires the user to complete a series of checklists.<br> I started the project using 2 tables per checklist - one table to store the questions and another to store the answers.</p> <p>But management keeps asking for more checklists so I changed my db approach (below). I'd like to know if this approach is good or I am setting myself up for trouble down the road. I am in the process of refactoring the whole application because it did not follow an MVC approach. Now is the time to correct any database deign issues.</p> <p>Here is my new approach:</p> <p>tables:</p> <p><code>CHECKLIST_CLASS</code> - store a type of checklist i.e. Quality Audit Checklist, Process Audit Checklist<br> <code>CHECKLIST_INSTANCE</code> - the users checklist being worked. store FK of the checklist_class_id, work_flow_id<br> <code>CHECKLIST_ANSWER</code> - stores the check_list_instance_id, FK of the question_id, multiple choice answer<br> <code>CHECKLIST_QUESTION</code> - stores the question_id, FK of the checklist_class_id </p> <p>I like this approach because I can dynamically add a new check list without adding another db table.<br> but it makes queries a little complicated looking for determining the status of each checklist in the workflow.</p> <pre><code> SELECT TO_CHAR(CALCDATEREQUIRED, 'MM/DD/YYYY') as CALCDATEREQUIRED, TO_CHAR(CALCAPPROVEIPRDATE, 'MM/DD/YYYY') as CALCAPPROVEIPRDATE, (SELECT SECTION_I_STATE FROM TPQOT_CALC_MODEL WHERE CHECKLIST_INSTANCE_ID = SELECT MAX(TPQOT_CHECKLIST_INSTANCE.CHECKLIST_INSTANCE_ID) FROM TPQOT_CHECKLIST_INSTANCE WHERE TPQOT_CHECKLIST_INSTANCE.CHECKLIST_CLASS_ID='1257877690209' AND TPQOT_CHECKLIST_INSTANCE.CALC_ID=CALCID AND TPQOT_CHECKLIST_INSTANCE.CALC_REV=CALCREV) ) AS SECTION_I_STATE, (SELECT CHECKLIST_STATE AS FINALIZE_CHECKLIST_STATUS FROM TPQOT_CHECKLIST_INSTANCE WHERE CHECKLIST_INSTANCE_ID = (SELECT MAX(TPQOT_CHECKLIST_INSTANCE.CHECKLIST_INSTANCE_ID) FROM TPQOT_CHECKLIST_INSTANCE WHERE TPQOT_CHECKLIST_INSTANCE.CHECKLIST_CLASS_ID='1257877690209' AND TPQOT_CHECKLIST_INSTANCE.CALC_ID=CALCID AND TPQOT_CHECKLIST_INSTANCE.CALC_REV=CALCREV) ) AS DI4630901_STATE, (SELECT CHECKLIST_STATE AS FINALIZE_CHECKLIST_STATUS FROM TPQOT_CHECKLIST_INSTANCE WHERE CHECKLIST_INSTANCE_ID = (SELECT MAX(TPQOT_CHECKLIST_INSTANCE.CHECKLIST_INSTANCE_ID) FROM TPQOT_CHECKLIST_INSTANCE WHERE TPQOT_CHECKLIST_INSTANCE.CHECKLIST_CLASS_ID='1257877670188' AND TPQOT_CHECKLIST_INSTANCE.CALC_ID=CALCID AND TPQOT_CHECKLIST_INSTANCE.CALC_REV=CALCREV) ) AS OPC_STATE, (SELECT phonebook.lname FROM phonebook WHERE phonebook.badge = xtbl463Calc.CalcOriginator) AS OrigName, (SELECT phonebook.lname FROM phonebook WHERE phonebook.badge = xtbl463Calc.CalcChecker) AS CheckName, (SELECT phonebook.lname FROM phonebook WHERE phonebook.badge = xtbl463Calc.CalcReviewer) AS ReviewName, (SELECT phonebook.lname FROM phonebook WHERE phonebook.badge = xtbl463Calc.CalcApprover) AS ApproveName FROM xtbl463Calc ORDER BY CALCID; </code></pre> <p>The result set for this query goes into an array list of hashmaps and sent to my view. Should I continue with this 4 table approach? Do I need to refactor my query? I think once I code all my javabean models representing the various checklists I can clean things up. I just want to feel like I am on the right path during this refactoring job.</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