June 10, 2010 at 11:55 am
I think the value of a seperate table is dependant on the case table. If this is a historical table then I think a seperate table is ver reasonable. Even small query could become problamatic over time if you have to query millions of rows every time a drop down is opened.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 10, 2010 at 12:09 pm
Thanks again all,
At this point, with guns pointing on my head to get this done, it really doesn't matter whether it is one or more tables.
BT, please take no offense when I said I didn't quite understand it.
What I was getting at was partly addressed by Scott, which you later handled with your revised code.
But the bigger part of my problem is NOT handled by either your code or revised table design.
The spec says that at a given wheel or round, during case assignment, no judge should have more than ONE case.
In other words, during round one, each of the 12 judges should have one case, and one case only, unless that case is Transfer Case, in which case it doesn't count.
We are not allowed to query the db to find out how many judges have the fewest cases, one case assignement per judge per round.
At the end of each round, the 12 judges are reassembled (reloaded) and case assignements start all over again.
Meanwhile, a table, could be historical which was what I had in mind with CasesArchive table, would be storing judges and ALL cases assigned to them, plus the clients.
If your original or revised code is doing that, I am missing it.
I want you and everyone helping me so far to understand how truly grateful I am for the assistance.
BTW BT: I wanted to show how close you and I are with your current revised table as compared to mine:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Cases]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Cases]
GO
CREATE TABLE [dbo].[Cases] (
[CaseID] [int] IDENTITY (1, 1) NOT NULL ,
[ClientID] [int] NULL ,
[JudgeCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CaseName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CaseType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IsTransferCase] [bit] NULL ,
[isNewCase] [bit] NULL
) ON [PRIMARY]
GO
IsNewCase is pretty much same as isActive
June 10, 2010 at 12:20 pm
In other words, during round one, each of the 12 judges should have one case, and one case only, unless that case is Transfer Case, in which case it doesn't count.
We are not allowed to query the db to find out how many judges have the fewest cases, one case assignement per judge per round.
At the end of each round, the 12 judges are reassembled (reloaded) and case assignements start all over again.
Hmm, I thought that's exactly what my code did.
First check for a transfer case. If that's true, assign it to the same judge, and ignore the "round"/"wheel" table.
If not a transfer case, randomly pick a judge from those still left for this round, assign the case to that judge, and delete the judge from the round.
When the round table is empty, reload all judges and start the process over.
That seems straightforward to me.
Btw, frankly I think a separate table for the judges for rounds is much simpler, and vastly more accurate, than trying to add a lot of switches to the main case table.
Scott Pletcher, SQL Server MVP 2008-2010
June 10, 2010 at 12:38 pm
scott.pletcher (6/10/2010)
Btw, frankly I think a separate table for the judges for rounds is much simpler, and vastly more accurate, than trying to add a lot of switches to the main case table.
Unnecessary denormalization is simply bad design. It may seem more straight-forward and more simple, but unless there is a very specific performance consideration, it's just a poor decision and this becomes only more true as the environment gets larger and more complex. If both options have roughly equivalent performance results you should always choose the normalized method.
And I'm curious, if my code is 100% accurate, how could something else be "vastly more accurate"? That doesn't seem possible.
June 10, 2010 at 12:47 pm
And I'm curious, if my code is 100% accurate
But your code is not accurate at all.
The total number of cases a judge has is ABSOLUTELY IRRELEVANT to assigning cases for the current round!
At least as I understand how it works.
Scott Pletcher, SQL Server MVP 2008-2010
June 10, 2010 at 12:54 pm
BT, Scott, it is very clear to me how great you are guys are with SQL.
I really appreciate it.
The one thing I still will like addressed, unless I am missing it and if I am, I beg you guys to forgive me because I am no where near you guys' skills/talent level with SQL.
But the one thing that I think with get me closer to the solution is that, going back to wheel/round thing, even if the judge is assigned gzillion Transfer Cases, the judge still stays on the wheel until a new case is assigned.
Again, one case per judge per round unless it is a transfer case.
If the judge gets the Transfer Case first, the judge is still available on the wheel until the one case-one judge quota is met; then the judge disappears.
I am really sorry if I appeart to NOT getting it"
June 10, 2010 at 1:05 pm
simflex,
with all the code flying around in this post, it's hard to keep straight. Can you provide the latest code that you are working with?
BT/Scott,
this forum is to try to help other with issues, not to get in a p*ssing contest as to whose code is better or who understands the issue better. The OP is ultimately the one who will tell you if it's right or not.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 10, 2010 at 1:06 pm
No problem. I get that. The *first* thing you do for a case is check whether it qualifies as a transfer case. If it does, assign it to the relevant judge and exit the code.
Only if it not a transfer case, then access the judgeround table to get a random judge.
I assume you have "case[history]" table of some kind you can check to determine if it's a transfer case.
So, something roughly like this to assign a judge to a new case:
DECLARE @nextJudgeID INT
SET @nextJudgeID = NULL
--chk if an active judge has had this defendant before
SELECT TOP 1 @nextJudgeID = c.judgeid
FROM case[history] c
INNER JOIN judges j on j.judgeid = c.judgeid and j.status = 'active'
WHERE c.defendant = @currentdefendant
IF @nextJudgeID IS NOT NULL
--prev. case before active judge found, this is a transfer case,
--assign to previous judge and exit w/o using judgeround table
...code to assign @nextJudgeID to current case...
GO TO ExitCode
END --IF
--code similar to before, assigning next random judge
--...if judgeround table is empty, reload it with all judges...
--...get random judge from judgeround table...
--...assign that judge to case...
--...delete that judge from judgeround table...
:ExitCode
Scott Pletcher, SQL Server MVP 2008-2010
June 10, 2010 at 1:19 pm
scott.pletcher (6/10/2010)
And I'm curious, if my code is 100% accurate
But your code is not accurate at all.
The total number of cases a judge has is ABSOLUTELY IRRELEVANT to assigning cases for the current round!
At least as I understand how it works.
You need to think outside the box you've created. You don't need to de-normalize the data to have a query return the judges that haven't been assigned a case in this round. Instead of a convoluted process, you can mathematically determine the results. Keep assigning judges and you'll keep seeing them disappear from the list. When the last one gets an assignment they'll all be back in the list. I don't need to maintain a stored table to house results I can derive mathematically.
You're confusing method with results. While you don't like the method and you want a de-normalized solution (which is fine, I just don't think it's the best answer for this scenario), saying the "code is not accurate at all" is either deceit or confusion on your part. My code returns the exact judges it should return and it will continue to do so for any list of possible inserts into the cases table that you can create.
I have offered plenty of suggestions on this site and others that have been superseded by better ones by other people. That's part of the process of getting better. But what I see in this thread is a dogged determination to stick to a particular implementation rather than focusing on the ultimate goal and then objectively evaluating potential implementations to reach that goal.
To the OP: good luck to you with whatever path you choose.
June 10, 2010 at 1:24 pm
Ok, if I may, can I please start with the table designs?
If you guys feel that the tables are ok, then I will begin to show the codes for each segment.
Judges table:
CREATE TABLE [dbo].[Judges](
[JudgeCode] [nvarchar](50) NOT NULL,
[JudgeName] [nvarchar](50) NULL,
[Status] [bit] NULL) ON [PRIMARY]
--Scott I just changed isNewCase to Status so we can have a value of Active or Inactive
****
Clients table:
CREATE TABLE [dbo].[Clients](
[ClientID] [int] NOT NULL,
[ClientName] [nvarchar](50) NULL
) ON [PRIMARY]
********
Cases Table --Originally using it to check for Transfer or not and to reload.
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]
Again, I can change is isNewCase to Status if you guys deem it necessary.
Reason I am using JudgeCode as nvarchar is so I can query just one table to show the judges their active cases and the admin can also see all cases including Transfer. The judges, we are told, are not supposed to see Transfer cases for other judges. I can change it back to int if you sugges so. I can then find another way to display info them, not a problem here at all.
******
CasesArchive table:
CREATE TABLE [dbo].[CasesArchive](
[CasesArchiveID] [int] NOT NULL,
[CaseID] [int] NOT NULL,
[ClientID] [int] NULL,
[JudgeCode] [nvarchar](50) NULL,
[CaseName] [nvarchar](50) NULL,
[CaseType] [nvarchar](50) NULL
) ON [PRIMARY]
I was going to use this to dump out all cases, active or Transfer and for display purposes.
Again, I can scratch you experts feel it isn't necessary.
If you guys don't mind, can you please help me get this right to save you guys time so we can all be on same page.
Again, many, many thanks
BT/Scott, I feel really, really bad for all issues that I can coming up and I am truly sorry for it.
As Dan stated, please bear in mind that I came to you guys for help and helping me, which you guys have been kind and generous so far should be the focus.
Please don't drop off. I believe you guys can help me through this.
Many ways to skin a cat and whichever solution helps me I am very happy to use because at the end of the day, that's why you guys have earned the honorable reputations you have here.
June 10, 2010 at 1:30 pm
OFF-TOPIC
My code returns the exact judges it should return and it will continue to do so for any list of possible inserts into the cases table that you can create.
Don't think so.
Why couldn't one judge have been there for, say, 60 years, and another judge for only 2 weeks? Wouldn't they have a different number of cases??
Assuming there's only ONE case table, including current and historical cases, how can you ever possibly use a total case count to decide which judge should get a new case?? It just doesn't work.
Scott Pletcher, SQL Server MVP 2008-2010
June 10, 2010 at 1:36 pm
You want to keep your judgeid as an INT, no need to use nvarchar for that.
Except for long-term archiving, you only need one case table that has both current and historical (closed) cases. If the table is large, you may need to add an index to that table on (defendant, judgeid) so that the lookup to check for a transfer case is not too slow.
Btw, I was thinking status for judge could also include:
active, sick, on-leave, on vacation, retired [some judges "retire" but continue to hear cases, some on a light work load [[that would be an issue for a "count"-based assignment method, huh?]] ], etc..
And you can apply whatever rules you apply in each specific status for assigning cases.
If you really want to avoid a "judgeround" table, you could add a "round" column to the judges table. At the start of a round, you clear every judge's round column (either to 0(unassigned this round) or a round#). As a judge is assigned for a round, you update the column to 1(assigned) or the round number.
When you try to find a judge who's unassigned in the round column and can't, then all judges have been assigned, so it's time to clear all the round columns and start a new round.
Scott Pletcher, SQL Server MVP 2008-2010
June 10, 2010 at 1:47 pm
I think a separate judgeround table is a better design in this situation because it's more flexible.
Say one judge is ailing and the others agree to give him/her a limited workload. Simple to do -- add a casecount to the judgeround table. When a judge is assigned, decrement the count. The ailing judge would get a case count of 1, the others, say, 2 -- all the healthy judges would then get twice as many cases as the ailing judge. Or naturally whatever ratio the judges agree on.
There are a hundred other variations that I believe are valid for which a separate table is easier to manage.
Yes, in a theoretical situation where nothing else ever came up, another table is not necessary. From my experience, in the real world, something always comes up sooner or later -- usually sooner 🙂 .
Scott Pletcher, SQL Server MVP 2008-2010
June 10, 2010 at 1:52 pm
scott.pletcher (6/10/2010)
Why couldn't one judge have been there for, say, 60 years, and another judge for only 2 weeks? Wouldn't they have a different number of cases??Assuming there's only ONE case table, including current and historical cases, how can you ever possibly use a total case count to decide which judge should get a new case?? It just doesn't work.
I'm really surprised that a SQL MVP wouldn't be able to figure out how to handle this issue without de-normalizing. I thought the program was more selective than that.
June 10, 2010 at 2:00 pm
OFF-TOPIC
So, how does your count method handle all the situations I've described?
It does NOT. It CANNOT. Your 'solution' only handles a pure vanilla situation. That's a college freshman design, not a professional design.
As I stated, I prefer not to put this into the judges table because I think in a real-life situation, where things come up for actual living people that don't follow theoretical guidelines, you need the flexibility of a separate table.
Remember, as soon as you add ANY other column of data the relates to the "round" process, by the rules of normalization, that will NEVER belong in a judges table.
Instead of making the mistake of using the judges table and then having to back out of it later, I choose to avoid the mistake altogether.
Scott Pletcher, SQL Server MVP 2008-2010
Viewing 15 posts - 16 through 30 (of 48 total)
You must be logged in to reply to this topic. Login to reply