Dealing with Duplicate Records
-
I am writing an IRC bot that keeps track of peoples usernames and ip address's whenever they join my channel. Its a pretty active channel so I am adding records quite often. I was just curious how I should deal with duplicate records. The way I see it, I have 3 options: 1. Before adding a record to my database, check to see if it already exists. 2. Add record to db, then once a week or something run a procedure to purge all duplicate records 3. Dont worry about having dupes in the DB. Option 3 is obviously the easiest :-D But least efficient (i think?:confused: ) I was planning on doing searching and reports against the data, so with that in mind, which option is my best bet?
-
I am writing an IRC bot that keeps track of peoples usernames and ip address's whenever they join my channel. Its a pretty active channel so I am adding records quite often. I was just curious how I should deal with duplicate records. The way I see it, I have 3 options: 1. Before adding a record to my database, check to see if it already exists. 2. Add record to db, then once a week or something run a procedure to purge all duplicate records 3. Dont worry about having dupes in the DB. Option 3 is obviously the easiest :-D But least efficient (i think?:confused: ) I was planning on doing searching and reports against the data, so with that in mind, which option is my best bet?
Why not add a constraint to your database and make their username a primary key, then check if the record exists before adding, and offer the user the chance to update their record ( assuming they have passed some check to ensure they are the original owner of the username ) Christian No offense, but I don't really want to encourage the creation of another VB developer. - Larry Antram 22 Oct 2002
C# will attract all comers, where VB is for IT Journalists and managers - Michael P Butler 05-12-2002
Again, you can screw up a C/C++ program just as easily as a VB program. OK, maybe not as easily, but it's certainly doable. - Jamie Nordmeyer - 15-Nov-2002 -
I am writing an IRC bot that keeps track of peoples usernames and ip address's whenever they join my channel. Its a pretty active channel so I am adding records quite often. I was just curious how I should deal with duplicate records. The way I see it, I have 3 options: 1. Before adding a record to my database, check to see if it already exists. 2. Add record to db, then once a week or something run a procedure to purge all duplicate records 3. Dont worry about having dupes in the DB. Option 3 is obviously the easiest :-D But least efficient (i think?:confused: ) I was planning on doing searching and reports against the data, so with that in mind, which option is my best bet?
Adding a UNIQUE constraint to that column, will prevent you from adding "another" item that is the same. :) WebBoxes - Yet another collapsable control, but it relies on a "graphics server" for dynamic pretty rounded corners, cool arrows and unlimited font support.
-
I am writing an IRC bot that keeps track of peoples usernames and ip address's whenever they join my channel. Its a pretty active channel so I am adding records quite often. I was just curious how I should deal with duplicate records. The way I see it, I have 3 options: 1. Before adding a record to my database, check to see if it already exists. 2. Add record to db, then once a week or something run a procedure to purge all duplicate records 3. Dont worry about having dupes in the DB. Option 3 is obviously the easiest :-D But least efficient (i think?:confused: ) I was planning on doing searching and reports against the data, so with that in mind, which option is my best bet?
The simple way to solve your problem is to run DELETE * FROM Table WHERE sUserName = 'whatever' INSERT INTO Table (...) VALUES (...) every time. This way you will have no dupes. You can also make a sorted procedure like IF EXISTS( SELECT * FROM Table WHERE sUserName = 'whatever' ) THEN UPDATE Table SET .... ELSE INSERT INTO Table (...) VALUES (...) END
-
I am writing an IRC bot that keeps track of peoples usernames and ip address's whenever they join my channel. Its a pretty active channel so I am adding records quite often. I was just curious how I should deal with duplicate records. The way I see it, I have 3 options: 1. Before adding a record to my database, check to see if it already exists. 2. Add record to db, then once a week or something run a procedure to purge all duplicate records 3. Dont worry about having dupes in the DB. Option 3 is obviously the easiest :-D But least efficient (i think?:confused: ) I was planning on doing searching and reports against the data, so with that in mind, which option is my best bet?
I see the fastest and easiest option as being number 1. However, what is your DB running on - Access, SQL? If you're running running on either of those (at least with SQL) it wouldbe extremely easy to implement option 1. Also, if your storing effectively a hashtable of usernames and IP's, there is rarley going to be dupes. I might sign in 5 times over 5 days and be listed as 5 unique entries for all of my new IP's. Very few IRC go'ers willhave a static IP. Tatham Oddie (VB.NET/C#/ASP.NET/VB6/ASP/JavaScript) tatham@e-oddie.com +61 414 275 989
-
I see the fastest and easiest option as being number 1. However, what is your DB running on - Access, SQL? If you're running running on either of those (at least with SQL) it wouldbe extremely easy to implement option 1. Also, if your storing effectively a hashtable of usernames and IP's, there is rarley going to be dupes. I might sign in 5 times over 5 days and be listed as 5 unique entries for all of my new IP's. Very few IRC go'ers willhave a static IP. Tatham Oddie (VB.NET/C#/ASP.NET/VB6/ASP/JavaScript) tatham@e-oddie.com +61 414 275 989
Tatham wrote: Very few IRC go'ers willhave a static IP. You mean except for those people who IRC from behind corporate gateways and those on Cable and DSL modems (with static IPs). :rolleyes:
-
Tatham wrote: Very few IRC go'ers willhave a static IP. You mean except for those people who IRC from behind corporate gateways and those on Cable and DSL modems (with static IPs). :rolleyes:
Reinout Hillmann wrote: who IRC from behind corporate gateways IRC isn't exactly the most popular thing behind a company firewall and use of it behind a COMPANY firewall means your not doing your job. Reinout Hillmann wrote: and those on Cable and DSL modems Not everybody has cable/DSL. Reinout Hillmann wrote: (with static IPs) Even with cable/DSL, if you turn the momem off then back on your IP can still change. FEW will have a static IP. Not NONE, FEW. Tatham Oddie (VB.NET/C#/ASP.NET/VB6/ASP/JavaScript) tatham@e-oddie.com +61 414 275 989