I have this table RC_CHAT:-
REFERRAL_ID BY_OFFICE TO_OFFICE STATUS_ID STATUS_DATE CHAT_ID PARENT_CHAT_ID
R1 KL12 KL11 3 05-01-14 C1
R1 KL12 KL13 3 06-01-14 C2
R1 KL13 KL01 3 07-01-14 C3
R1 KL11 KL12 1 08-01-14 C4 C1
R2 KL11 M001 3 09-01-14 C5
R2 M001 M002 3 10-01-14 C6
R2 M002 KL12 3 11-01-14 C7
R2 M002 M001 3 11-01-14 C8 C6
R2 M001 KL11 3 11-01-14 C9 C5
In this scenario, different offices communicate, discuss and decide(GRANT/DENY) concerned case(s) (each case uniquely identified by REFERRAL_ID). CHAT_ID is unique for every reply irrespective of the case. An office can either reply as a fresh message or as a reply to some earlier reply. PARENT_CHAT_ID will be null in former case while it would have the CHAT_ID of the immediate parent in the latter case.
The purpose of my application is to display all the cases in which an office is involved to that office upon logging in. The problem here is that we have millions of cases, multiplying to multi million replies. So, for ease of user access and interactivity I have to organize and categorize the cases for proper redressal of every case not making it an headache for the end user.
I have currently organized the cases into:-
1. Fresh (A case on which no reply has been posted by an office)
2. Under Process (A case on which a reply has been posted by an office but not granted/denied)
3. Disposed (A case which is granted/denied by an office). [STATUS_ID=1(Grant)/2(Deny)]
P.S.:- 3-Normal Message
Conditions:- A case at a point of time will be in different tabs for different offices.
In above example, case R1 will appear in:- "Fresh" for KL01. "Under Process" for KL12,KL13. "Disposed" for KL11.
Case R2 will appear in:- "Fresh" for KL12. "Under Process" for M001,M002,KL11. "Disposed" for none.
Like this, all the million cases are to be analysed and put into repective tabs in tabular format.
WHAT I EXPECT: The million or so cases should be sorted and displayed for various offices into appropriate tabs(Fresh, Under Process, Disposed) according to above given conditions.
MY QUESTION: What table structure or querying should I use to allow for this analysis is minimal time and cost? I have gone through various techniques available - Nesting sets, Connect By...Prior and others. But I am still clueless of their feasibility to my problem.