June 10, 2010 at 2:20 pm
Ok, thanks
So, then this would be good enough:
CREATE TABLE [dbo].[Judges](
[JudgeCode] [nvarchar](50) NOT NULL,
[JudgeName] [nvarchar](50) NULL,
[judgeround] [int] NULL,
[Status] [bit] NULL) ON [PRIMARY]
CREATE TABLE [dbo].[Cases](
[CaseID] [int] IDENTITY(1,1) NOT NULL,
[ClientID] [int] NULL,
[JudgeCode] [nvarchar](50) NULL,
[CaseName] [nvarchar](50) NULL,
[CaseType] [nvarchar](50) NULL,
[IsTransferCase] [bit] NULL,
[isNewCase] [bit] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Clients](
[ClientID] [int] NOT NULL,
[ClientName] [nvarchar](50) NULL
) ON [PRIMARY]
Initially, this is what I was doing:
my UI has the user to select Client from dropdown,
then type in case name and type in case type:
The params were grabbed at the processing page:
Please don't pay too much attention to html code.
' new case requested
client = Trim(Request("clientid"))
casename = Trim(Request("casename"))
casetype = Trim(Request("casetype"))
Then
If client <= 0 Then
' only generate a new client id if we *NEED* to!!!!!
Set Nocount on;
Insert INTO Clients (ClientName) VALUES ('" & Replace(Request("newclientid"),"'","''") & "');
Select @@identity;
set nocount off
grab latest clientID
End If
Above means that if the dropdown doesn't have the clientID we are trying to select from it, then add it so it will be there next time we need it.
--so that we can now create a case and store on Cases table.
INSERT INTO Cases (clientid,casename,casetype) " _
VALUES(" & client & ",'" & casename & "','" & casetype & "')
--Again, get the recently inserted caseID
SELECT @@IDENTITY"
caseid = RS(0)
--Now that we have created a case, let's assign it to a judge.
--Judges dropdown is now visible:
paramcaseid = Trim(Request("caseid"))
ParamJudgeCode = Trim(Request("Judge"))
UPDATE Cases SET JudgeCode = '" & ParamJudgeCode & "' WHERE caseid = " paramcaseid
--so far so good.
' now see if there are any cases pending that need a Judge assigned to them. If it is the first time that this case is being assigned, JudgeID should be null or 0
SELECT TOP 1 CaseID, ClientID, casename, casetype FROM Cases WHERE Coalesce(JudgeCode,0) = 0 ORDER BY CaseID
First time a case is created, JudgeId is either null or 0 which means that at this time, this case has not been assigned to a judge yet.
After the update, and the client dropdown or wheel is again, we want create another case. The same client and others are still there (will always be there.)
If we select same client again, we want to check again to see if the client has ever been assigned to judge before. If yes, it goes to same judge. If no, we see list of judges again to select from:
This time we run this code:
' now, does this client have any cases assigned to any Judge?
SELECT TOP 1 JudgeCode FROM Cases WHERE clientID = " & clientid & " AND Coalesce(JudgeCode,0) <> 0
Note that this code is run same time as the one above where judgecode = 0
lcode = RS(0) ' yes! already has this code assigned
SQL = "UPDATE Cases SET JudgeCode = '" & lcode & "', isTransferCase = 1 WHERE caseid = " & paramcaseid
This is the code that goes with the judges initial dropdown and the judges keep dropping off from this dropdown till all of them have been assigned a case each.
--As long as there are judges in this dropdown, keep assigning each a case till they are all gone.
SQL = "SELECT j.JudgeCode, j.JudgeName
FROM Judges j
LEFT JOIN Cases c ON c.JudgeCode = j.JudgeCode
WHERE c.JudgeCode is null or c.isTransferCase = 1 "
ORDER BY j.JudgeName"
Then reload the dropdown:
ELSE 'No Judges Available, time to reload and start over
--this is one of the problem areas
SELECT DISTINCT j.JudgeCode, j.JudgeName
FROM Judges j
LEFT JOIN Cases c ON c.JudgeCode = j.JudgeCode
WHERE c.JudgeCode is null OR c.isTransferCase = 1
"ORDER BY j.JudgeName
I don't know if I am doing this the right way but I wanted to show each step what I am doing.
What would be helpful, if possible, is to show where changes are needed, especially as far as reloading the wheel is concerned.
June 10, 2010 at 2:31 pm
You have two different approaches you can take:
1) Leave the base Judge and Case tables alone; instead, create a separate table to control rounds. This is the method I prefer.
The Judge will contain a status, which can have several values.
The Case should include a Transfer flag, since that info relates to the case. But the case table will not have round info, since that does not relate directly to the case in any way.
Round assignment uses a simple query on a completely separate table. The main tables are not "corrupted" in any way with misplaced columns.
2) Alter the Judge and Case tables to store round info. Use a complex query to determine which Judge goes next.
You have chosen option (2). All well and good. Hopefully the other poster will help you with that method.
In my view, I think you will have lots of trouble with that method in the future, so I hope he continues helping you. Once you corrupt your tables with improperly placed columns, you usually have query issues within a relatively short time.
Scott Pletcher, SQL Server MVP 2008-2010
June 10, 2010 at 2:54 pm
No, I have not chosen anything at all.
That's why I posted the tables I have so far so you can help me redesign them the way you think will help me accomplish my goal here.
So, no, nothing is decided yet.
I am willing to start completely all over.
I just need help with any approach that works.
So, please tell me what needs to change with what I have posted so far starting with table redesign.
Again, many, many thanks
June 10, 2010 at 3:27 pm
I suggest this something like this for the tables:
DROP TABLE Cases
DROP TABLE clients
DROP TABLE Judges
CREATE TABLE [dbo].[Judges](
[JudgeID] [int] NOT NULL CONSTRAINT Judges_PK PRIMARY KEY,
[JudgeName] [nvarchar](50) NULL,
[Status] [tinyint] NULL, --chg: 1=normal/active; 4=on vacation; ...etc...; 250=retired.
[IsTakingNewCases] [bit] --add
)
CREATE TABLE [dbo].[Clients](
[ClientID] [int] NOT NULL CONSTRAINT Clients_PK PRIMARY KEY,
[ClientName] [nvarchar](50) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Cases](
[CaseID] [int] IDENTITY(1,1) NOT NULL CONSTRAINT Cases_PK PRIMARY KEY,
[ClientID] [int] NOT NULL CONSTRAINT Cases_RF_Clients REFERENCES Clients,
[JudgeID] [int] NULL,
[CaseName] [nvarchar](80) NULL,
[CaseType] [nvarchar](50) NULL,
[IsTransferCase] [bit] NULL,
[DateCreated] [datetime] NULL DEFAULT GETDATE() --added
) ON [PRIMARY]
CREATE INDEX Cases_IX_JudgeID ON Cases (JudgeID)
--add
CREATE TABLE [dbo].[Round] (
[JudgeID] [int]
)
If a case has to move from one judge to another, I'm assuming it's ok to "forget" about the prior judge and just have the case as assigned to the new/current judge.
Scott Pletcher, SQL Server MVP 2008-2010
June 10, 2010 at 5:40 pm
Thanks very much Scott.
I am fine with these changes.
Infact, I have modified my db so they now like these.
How do I approach the code from here?
Do I trash the codes I posted?
Do we make changes to them wherever necessary?
Please guide me.
Again, many, many thanks.
PS: I had to leave before security kicked me out to luck the door.
June 11, 2010 at 9:01 am
CORRECTION:
Instead of:
CREATE INDEX Cases_IX_JudgeID ON Cases (JudgeID)
Should be:
CREATE INDEX Cases_IX_01 ON Cases (ClientID, JudgeID)
Scott Pletcher, SQL Server MVP 2008-2010
June 11, 2010 at 9:39 am
For a new case, the client and case parts work exactly as they do now.
[Note that my design currently means "no client, no case", that is, a case *must* have a client assigned (the REFERENCES clause forces this). [If you ever need to create a case before assigning the client(?), add a client with a number 0 to the client table; that can act as a placeholder client until the real one is assigned.]]
For the judge part:
--Check if current client has another case with a judge assigned.
DECLARE @newJudgeID INT
SET @newJudgeID = NULL
SELECT TOP 1 @newJudgeID = JudgeID
FROM Cases
WHERE clientID = @newClientID
AND (JudgeID IS NOT NULL AND JudgeID > 0)
IF @newJudgeID IS NOT NULL
--previous case w/ judge found, make it a transfer case and assign
--same judge as other case, w/o affecting current round of judges
BEGIN
UPDATE Cases
SET isTransfer = 1, judgeID = @newJudgeID
WHERE caseID = @newCaseID
END --IF
ELSE
BEGIN
--no previous case w/ judge found, so assign judge from existing round
IF NOT EXISTS(SELECT TOP 1 * FROM Round)
BEGIN
--no more judges left in round, reload all judges for next round
INSERT INTO Round (judgeID)
SELECT judgeID
FROM Judges
WHERE status = 1 AND isTakingNewCases = 1
END --IF
SELECT judgeID
FROM round
--********--???--not sure how this part works
--display avail. judges
--they select one
--assign that judge to the case
UPDATE Cases
SET judgeID = @newJudgeID
WHERE caseID = @newCaseID
--this **MUST** be done to remove just-assigned judge from current round
DELETE FROM Round
WHERE judgeID = @newJudgeID
END --ELSE
Scott Pletcher, SQL Server MVP 2008-2010
June 11, 2010 at 10:17 am
Hi Scott,
Thanks.
I have made the change - index change.
I guess I don't understand why they have to pick a judge from a dropdown vs. you just randomly assigning one. Couldn't you just randomly assign a judge from those remaining and notify them which one was assigned?]
Yes, infact, they explicitly used the language random judge assignement.
I thought the way I was doing was doing just that.
How we do we randomly assign it, by using NEWID()?
Sorry, it is a silly question.
[If you ever need to create a case before assigning the client(?), add a client with a number 0 to the client table; that can act as a placeholder client until the real one is assigned.]]
We create the client first; then the case.
I will work with what you have so far.
I will post back with results/questions.
I am still trying to remember the first time I came across that name = Scott Pletcher; a very famous and respected name.
Thank you for your help.
June 11, 2010 at 10:27 am
...other code same as before...
ELSE
BEGIN
--no previous case w/ judge found, so assign judge from existing round
IF NOT EXISTS(SELECT TOP 1 * FROM Round)
BEGIN
--no more judges left in round, reload all judges for next round
INSERT INTO Round (judgeID)
SELECT judgeID
FROM Judges
WHERE status = 1 AND isTakingNewCases = 1
END --IF
--randomly select a judge from those left in this round
SELECT TOP 1 @newJudgeID = judgeID
FROM round
ORDER BY NEWID()
--IF @@ROWCOUNT = 0 **ERROR**SHOULD NEVER HAPPEN**
--assign that judge to the case
UPDATE Cases
SET judgeID = @newJudgeID
WHERE caseID = @newCaseID
--**MUST** remove just-assigned judge from current round
--(otherwise he/she could be assigned again before other judges)
DELETE FROM Round
WHERE judgeID = @newJudgeID
--++++++--add code here to display @newJudgeID's name to user
END --ELSE
Scott Pletcher, SQL Server MVP 2008-2010
June 11, 2010 at 10:28 am
I am still trying to remember the first time I came across that name = Scott Pletcher; a very famous and respected name.
Maybe Experts-Exchange? I did *a lot* of posting there for several years. (Although I took a year off recently, from Apr 09 - Apr 10.)
Scott Pletcher, SQL Server MVP 2008-2010
June 11, 2010 at 12:08 pm
I've read this thread, and it seems everyone wants to jump right into code without fleshing out the domain and entity relationship model, and it seems to me that's leading to some of these circular arguments. Simflex, it seems you're on your way to getting a system that works to your requirements by your deadline, so don't let me cause you to change paths at such a late date. But recognize that doing ERM before starting to code could have helped you before you had a semi-working system that you now have to somehow bolt additional code around.
But that said, after having read the 38 posts, here are some comments that I would have asked as entity modelling questions at the beginning of the project.
The term "wheel" or "round" as a candidate entity is standing out, party because it has the look of a domain-expert term. Is a "wheel" a distinct entity with persistence? It certainly makes sense as an association entity to enforce the business rule that each judge gets assigned an equal number of new cases; does the domain only care about the wheel during assignment, or should we know any attributes about "Wheel 12, case 6" in the future? My local Sheriff has a sequence of "Books" of foreclosure cases, where each Book holds a fixed number of foreclosures, and becomes a real world key value for how that office manages their elements of the caseflow. IE, Book #123 fills up with initial filings at the same time, but today's auction for cases that have reached the auction endpoint of the process could be items from distinct books. Having a record of the assignment events would make meeting the even-assignment rule pretty easy, and it would also allow for auditing that the rule is being observed.
Judges: what matters, the individual, or the seat? The fact that there will always be 12 is telling. One of the earlier comments got into a digression of handling death, retirement, etc; however, it's my understanding that at least the US Circuit Courts do not work this way -- the count of circuit judge seats is set by Congress by statute, and on the death or retirement of a judge, the replacement takes the existing case load (with the president judge having power to reassign or substitute during the vacancy); in other words the person is a slowly changing attribute, and it's the seat that matters. Each court system could have different rules, so the specific domain is what's important.
Cases and Clients: are these distinct entities? We all seem to want to say yes, but the business rule given "The other caveat, which makes this a bit harder is that if client is assigned to a judge at some time be it last year or 10 years for a particular case, if that client comes back again, by default, the judge that handled that case will take up that case again but it will not count as a new assignment." says to me that maybe the case and the client are the same entity, or at least have a 1:1 relationship. As pure assumption, if the court domain being modeled involves social work cases, guardianship of minors, parole, or probation officers, this could make sense, the person could be the case.
June 11, 2010 at 1:17 pm
I agree with Keith - make sure you know what you are really trying to solve for.
Cases may have open, assigned, closed statuses, along with a unique id and a Client id.
There should problably be a Clients table.
Judges might be a table with unique id and name.
Where you want to store the Judges and assigned cases could easily be a 3rd table.
Give some thought to the scenario of how you assign cases for repeat clients.
If you have a round everytime 12 cases are unassigned, and they are all for the same client, what is your outcome?
There can be many scenarios, depending on what the true requirements are.
This sounds like schoolwork to see how well you can deal with desiging something that will probably be able to handle some different situations not spelled out directly in the intial task.
Greg E
June 11, 2010 at 2:02 pm
YES!
Experts-Exchange!!
I was actually thinking either Experts-Exchange or Tek Tips.
The legends of this forum continues to grow as it is loaded with superb talents.
Hope to get there one day.
In the last post, you said, same as old.
Only difference is the NEWID() for random selection?
June 11, 2010 at 2:06 pm
Yep. NEWID() will generate a random value [ok, for the hyper-technical, no intel server can generate a truly random number, but it's good enough for this :-)]. So if you sort by NEWID(), you get a random sort, so picking the TOP 1 from that sort gives you a random judge 🙂 .
Scott Pletcher, SQL Server MVP 2008-2010
June 11, 2010 at 2:18 pm
Keith and Greg.
Thanks a lot for your inputs.
I grappled and fretted with this design for a long time and asked a lot of the questions you came up with.
Eventually, I came up with the design I had.
I am extremely confident that whatever was missing has been addressed by Scott's revised design.
One great question Keith asked was whether Judges's asignment was by name or by seat.
It was actually seat (code). This way, if that seat becomes vacant for whatever reason, another judge replaces it and the change is transparent to the user.
The question of how many judges are selected or the criteria by US Circuit doesn't seem to be an issue here,
This selection is based on most serious offenses, mostly, murder cases.
Viewing 15 posts - 31 through 45 (of 48 total)
You must be logged in to reply to this topic. Login to reply