Just Software Solutions

Database Tip: Eliminate Duplicate Data

Friday, 25 January 2008

Storing duplicated data in your database is a bad idea for several reasons:

  1. The duplicated data occupies more space — if you store two copies of the same data in your database, it takes twice as much space.
  2. If duplicated data is updated, it must be changed in more than one place, which is more complex and may require more code than just changing it in one location.
  3. Following on from the previous point — if data is duplicated, then it is easy to miss one of the duplicates when updating, leading to different copies having different information. This may lead to confusion, and errors further down the line.

Coincidental Duplication

It is worth noting that some duplication is coincidental — it is worth checking out whether a particular instance of duplication is coincidental or not before eliminating it. For example it is common for a billing address to be the same as a delivery address, and it may be that for all existing entries in the table it is the same, but they are still different concepts, and therefore need to be handled as such (though you may manage to eliminate the duplicate storage where they are the same).

Duplication Between Tables

One of the benefits of Using an artificial primary key is that you can avoid duplication of data between the master table and those tables which have foreign keys linked to that table. This reduces the problems described above where the duplication is in the foreign key, but is only the first step towards eliminating duplication within a given table.

If there is duplication of data between tables that is not due to foreign key constraints, and is not coincidental duplication, then it is possibly worth deleting one of the copies, or making both copies reference the same row in a new table.

Duplication Between Rows Within a Table

Typically duplication between rows occurs through the use of a composite primary key, along with auxiliary data. For example, a table of customer orders might include the full customer data along with each order entry:
CUSTOMER_ORDERS
CUSTOMER_NAMECUSTOMER_ADDRESSORDER_NUMBERITEMQUANTITY
Sprockets LtdBooth Ind Est, Boston200804052Widget 23450
Sprockets LtdBooth Ind Est, Boston200804052Widget Connector900
Foobar IncBaz Street, London200708162Widget Screw size 5220
Foobar IncBaz Street, London200708162Widget 4255

In order to remove duplication between rows, the data needs to split into two tables: the duplicated data can be stored as a single row in one table, referenced by a foreign key from the other table. So, the above example could be split into two tables: a CUSTOMER_ORDERS table, and an ORDER_ITEMS table:
CUSTOMER_ORDERS
CUSTOMER_NAMECUSTOMER_ADDRESSORDER_NUMBER
Sprockets LtdBooth Ind Est, Boston200804052
Foobar IncBaz Street, London200708162
ORDER_ITEMS
ORDER_NUMBERITEMQUANTITY
200804052Widget 23450
200804052Widget Connector900
200708162Widget Screw size 5220
200708162Widget 4255

The ORDER_NUMBER column would be the primary key of the CUSTOMER_ORDERS table, and a foreign key in the ORDER_ITEMS table. This isn't the only duplication in the original table, though — what if one customer places multiple orders? In this case, not only are the customer details duplicated for every item on an order, they are duplicated for every item on every order by that customer. This duplication is still present in the new schema, but in this case it is a business decision whether to keep it — if a customer changes address, do you update the old orders with the new address, or do you leave those entries alone, since that was the address that order was delivered to? If the delivered-to address is important, then this is coincidental duplication as described above, if not, then it too can be eliminated by splitting the CUSTOMER_ORDERS table into two.

The Downsides of Eliminating Duplication

The benefits of eliminating duplication might seem obvious, but there are potential downsides too. For example:

  • If the application is already released, you need to provide upgrade code to change existing databases over to the new schema without losing any data.
  • If you split tables in order to reduce duplication, your SQL can get more complicated, as you need more table joins.

Conclusion

As with everything in software development it's a trade-off. However, as the database gets larger, and more data gets stored, the costs of storing duplicate data increase, as do the costs of changing the schema of an existing database. For this reason, I believe that it is worth designing your schema to eliminate duplication as soon as possible — preferably before there's any data in it!

Posted by Anthony Williams
[/ database /] permanent link
Tags: ,

| Stumble It! stumbleupon logo | Submit to Reddit reddit logo | Submit to DZone dzone logo

Comment on this post

If you liked this post, why not subscribe to the RSS feed RSS feed or Follow me on Twitter? You can also subscribe to this blog by email using the form on the left.

4 Comments

Very awesome article about deleting the duplicate data in databases.. Recently, i got a problem duplication between rows in a table. I don't know how to solve it, After reading the article i got the solution for that. It is very useful for me.

Keep on the good work..

Thanks a lot......

by Ashley at 10:08:24 on Monday, 15 February 2010

can you the answer for "whether the record matching over the query results is possible in doing across web." and if it is so "how to eliminate the duplicates by providing ranking"

by pavithra at 10:25:22 on Monday, 27 September 2010

Very nice article. Nowadays, eliminating a duplicate data is become a very easy and effort less through use of data deduplication software.

by Mathew Rotlen at 11:20:10 on Monday, 20 January 2014

I had for the first time insisted on this website to get an idea how to get gta 5 money online with these generator.

by gta 5 free money at 19:09:31 on Tuesday, 17 January 2017

Add your comment

Your name:

Email address:

Person or spambot?

Your comment:

Design and Content Copyright © 2005-2018 Just Software Solutions Ltd. All rights reserved. | Privacy Policy