Trigger to evaluate tricky duplicate
-
Here is my situation:
I have a database that tracks mixed-martial-arts fighters, events, and results.
I have a table called dbo.Fight that tracks individual fights at events.
I have a column in that table to enter the event at which the fight takes place: EventID
I have two columns in that table to enter the fighters: Fighter1ID, and Fighter2ID
The table has a Primary key: FightID.
The table also has a unique index based on: EventID, Fighter1ID, Fighter2ID.
The unique index ensures that you cannot enter the same fight at the same event twice.
However, there is a possibility that the fighters could be entered in alternate columns, which would be a duplicate.
Consider:
EventID Fighter1ID Fighter2ID
1 5 10
If I try to enter the same data, the unique index will not allow it.
However, it will allow this:
EventID Fighter1ID Fighter2ID
1 10 5
Can anyone propose a solution? My first thought was to use an AFTER INSERT, UPDATE trigger, but I’m not sure what sort of expression to use.
I need something that will evaluate whether the same fight exists, but with fighters in opposite columns.
So, for example, if Bill fights Ted at Event 1, I want the trigger to catch Ted fights Bill at Event 1.
Any help would be great.
Thanks,
Simon
-
I'm thinking that your table is a bit denormalized... it should be like this...
EventID Fighter 1 5 1 10 2 4 2 6
And the primary key should be (EventID,Fighter). But, that's a whole 'nother story... let's see if we can use what we have... I'm not going to write trigger code, but we'll setup a test to simulate that you can convert to trigger code... and, I think you should use an INSTEAD OF trigger, instead...
--===== Create a temp table to simulate the "FIGHT" table CREATE TABLE #Fight (EventID INT, Fighter1 INT, Fighter2 INT) INSERT INTO #Fight (EventID,Fighter1,Fighter2) SELECT 1,5,10 UNION ALL SELECT 2,4,6
--===== Create a temp table to simulate the "INSERTED" table of a trigger CREATE TABLE #Inserted (EventID INT, Fighter1 INT, Fighter2 INT) INSERT INTO #Inserted (EventID,Fighter1,Fighter2) SELECT 1,10,5
--===== Do the insert from an "INSTEAD OF TRIGGER" (simulated, of course) INSERT INTO #Fight SELECT i.EventID, i.Fighter1, i.Fighter2 FROM #Inserted i WHERE NOT EXISTS (--==== Checks for the reversed situation SELECT 1 FROM #Fight f WHERE f.EventID = i.EventID AND f.Fighter1 = i.Fighter2 AND f.Fighter2 = i.Fighter1 )
Like I said, earlier... I think your table is denormalized and it would be better if you fixed the Fight table, instead.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks so much for your reply, Jeff.
For your suggestion regarding normalizing my FIGHT table, should I normalize it even if there will always be two fighters in a fight?
-Simon
-
Sure... look at the problems you're having now with trying to keep the data unique. Which do you think will be more effecient... doing a two line insert (INSERT/SELECT UNION ALL would be best here) for each event or doing a correlated sub-query in a trigger to see if you can do the insert and then doing the insert? You can get rid of that by normalizing the table and establishing the proper primary key.
I will admit, though, I haven't thought about how to enforce the two line limit per fight. Of course, normalizing the table like this will also allow for tag-team fights...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
If I normalized the table, wouldn't I have duplicate values in all the other fields, besides Fighter?
For example:
EventID Fighter Date FightOrder ScheduledRounds WeightClass Result ResultRound ResultTime .... ....
1 5 1/1/07 5 3 170 1 2 3:22
1 10 1/1/07 5 3 170 1 2 3:22
If I did normalize, but with a much smaller Fight table (just EventID, Fighter), and kept the other columns (e.g. ones listed above) in a related table, wouldn't that complicate things more for insertions and retrievals? Unless, as you suggested, tag-team fights are implemented, then there would be no choice!
Your suggestion to change my primary key is great: I'm going to change it to (EventID, Fighter1ID, Fighter2ID).
As for your coding solution for checking for reversed duplicates, I'm going to try it this morning, and will let you know how it goes.
Thanks for all your help,
- Simon
-
Normalization would dictate you split the event data itself (date, bout #, rounds, time, weightclass) from the fighters in the event (fighterID, weight). That's theory talking of course.
As to limiting the limits to no more than two, if you throw a bit field into there (like - who won, or who wore the "white trunks" that day), and put a unique index in play - you'll prevent any more than 2 people in any given bouts. unique index would be eventid+fighterid+who_won (I'm not crazy about having a bit field in an index - but it's a compound index, so it should still work).
Of course - some validations might not make the DB (like - a fighter fighting himself probably shouldn't be legit), or there being FEWER than two fighters. These are actually very easy to implement up front (i.e. in whatever front-end is putting the data into SQL).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Thanks, everyone, for all the input.
I have decided not to normalize the Fight table, because there will always only ever be two fighters in the context of this database.... (famous last words!)
Here's what I have done so far:
(1) To make sure that a fighter cannot fight himself, I created a table-level check constraint ([Fighter1ID]<>[Fighter2ID]).
(2) I had previously created a unique non-clustered compound index on (EventID, Fighter1ID, Fighter2ID) to make sure a fight between the same two people can't happen twice in one event, but will probably change that to make it my primary key.
(3) To make sure that the same fight at the same event isn't entered in reverse-order, I created an INSTEAD OF trigger. Here is the syntax (with thanks to Jeff Moden):
CREATE TRIGGER Trig_FightNoDupes
ON
dbo.FightINSTEAD
OF INSERTAS
INSERT
INTO dbo.Fight(EventID
,FightOrder
,SchedRounds
,WeightClass
,TitleFight
,Fighter1ID
,Fighter2ID
,Result
,ResultMethod
,ResultRound
,ResultTimeMin
,ResultTimeSec
,FightComments
)
SELECT
EventID
,FightOrder
,SchedRounds
,WeightClass
,TitleFight
,Fighter1ID
,Fighter2ID
,Result
,ResultMethod
,ResultRound
,ResultTimeMin
,ResultTimeSec
,FightComments
FROM
INSERTED AS IWHERE
/* Ignore the transaction if the fight already exists in reverse-order */
NOT
EXISTS(SELECT 1FROM dbo.Fight AS FWHERE F.EventID = I.EventIDAND F.Fighter1ID = I.Fighter2IDAND F.Fighter2ID = I.Fighter1ID)/* Print message to user if the record is not added */
IF
(SELECT COUNT(*)FROM INSERTED AS I, Fight AS FWHERE F.EventID = I.EventIDAND F.Fighter1ID = I.Fighter2IDAND F.Fighter2ID = I.Fighter1ID) > 0PRINT
'This fight at the same event exists in reverse-order. The record will not be added.'
So far everything's working great. However, instead of using PRINT at the last part of my INSTEAD OF trigger, should I use the RAISEERROR function?
Thanks to everyone for their input and advice.
- Simon
-
Just curious - you could probably achieve the same thing if you made the data entry app "force" the fighterID's to be in a specific sequence (such as - FighterID1 is always the SMALLER of the two fighter ID's). If you can pull that off - you can ensure that you don't mess up with duplicates, just by using the index you already have in place).
You could even use one constraint in the DB to cover both this and the fighter fighting himself (a.k.a constraint would be "fighterID1<fighterID2" ).
Either way - if you wish to capture the error - RAISEERROR is better, since it would log to the event log. I can't say I've tried using a Print inside of a trigger - but my spidey sense is telling me that would be a bad thing.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
That's an interesting idea, Matt, except that in some events, the Fighter1ID column needs to be the Champion, and the Fighter2ID column needs to be the challenger (in the case of some title fights, for example). In other cases, such as Country vs Country events or TeamA vs TeamB events, fighters need to be in certain (seemingly arbitrary) positions. Neat idea to use the one constraint to catch both cases, though -and much simpler.
I will investigate the syntax for RAISEERROR (my first forray in that area), and hopefully come-up with something more eloquent than the simplistic PRINT statement.
-
Are you constructing this project for fun or business? The reason I'm asking is that if its a business project, you will save yourself alot of grief by taking the time to normalize the data. If this is for fun, then you have the luxury of not having a deadline. Without a deadline you could take the time to learn about data normalization. Even if you already know how to normalize, you have a great opportunity to reinforce this knowledge! Either way, normalizing is the winning direction! Good luck!
-
The project is being used for fun, and as a learning tool/active workshop.
When you say "take the time to normalize the data", I'm assuming that you mean normalizing part of the Fight table, as suggested by others earlier.
In your opinion, is it really worth it to normalize part of the Fight table, even if there will always only be two fighters in a fight?
Won't that complicate things?
-Simon
-
It depends on your motivation. Since it's both fun and learning, try it both ways. Keep your current structure and adjust primary keys accordingly, then play with setting up some test tables for the normalization.
As to whether it's worth it? Sure, if you've got the time. You know exactly what you want to end up with, which is the best way to start a database design project. But here's a question. What if you suddenly decide you want to do this for pro wrestling. How ya gonna record a cage match with eight fighters? That's why you normalize. Sometimes you can't anticipate exponential growth. If you hard code columns as Fighter1 and Fighter2 you limit the growth. If the design is flexible enough, (ie normalized) you would be able to accomodate online MMOs if you wanted to without any database design changes.
Have fun however you decide!
-
Robert:
Thanks for your advice, I appreciate it.
You weren't the first one to mention tag-team/cage match scenarios, which makes me wonder if there's a lobby group for tag-team/cage matches nested within SqlServerCentral that I'm not aware of....
-
Heh... in my case, it's multiple ex-wifes
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
That's a tag team I'm NOT commenting on
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply