how can reduce duplication in database?

**chamadii** · 06-25-2012, 04:59 AM

This time i really need suggestion tell me about how can i reduce duplication in database.

**The Little Guy** · 06-25-2012, 09:08 AM

the best way is to add unique keys to your table. So, depending on what columns need to be unique is what you would put the key on examples:

email, username -- This would require that the email and username be unique.
- me@mysite.com, kitty is not the same as me@mysite.com, cat both are unique
- me@mysite.com, kitty is not the same as my@site.com, kitty both of these are unique

But, if we were to split these into two different unique keys

email -- This would require that only the email be unique
username -- This would require that only the username be unique

Now each one individually needs to be unique, whereas the first example both the email and username needed to be unique. I hope that makes sense.

now when we do our query....

Code:

insert ignore into members (email, username) values ('me@mysite.com', 'kitty');

that will insert into the database UNLESS it isn't unique, then nothing will be inserted.

Or we can do:

Code:

insert into members (email, username) values ('me@mysite.com', 'kitty') on duplicate key update member_count = member_count + 1;

Usually you wouldn't have this on a members table, but to get the example across, if you have a duplicate key, you can update the duplicate row instead of doing an insert.