3
$\begingroup$

My Computing teachers talk about data duplication and data redundancy in terms of databases as different things, but I'm struggling to differentiate them. The context is database normalisation, and in particular why the third normal form is important: because it eliminates data duplication and redundancy. But as far as I can see, both of these terms relate to data appearing more than once in a database. Yes, I asked my teachers, but I didn't understand the explanation and it was a group revision session that I didn't want to hold up.

$\endgroup$
  • 1
    $\begingroup$ That's why they have office hours :) $\endgroup$ – Tom Zych Jun 16 '14 at 10:02
6
$\begingroup$

(1) Duplication

When you create tables in a database, you may want to create duplication:

CREATE TABLE user (name TEXT, id INTEGER);
CREATE TABLE friends (name TEXT, friend_of_id INTEGER);

Here you would duplicate the name of the user in both tables. That way when you display the list of friends, you do not have to access the user table to read the name. This is a simple example, but it shows what we call data duplication.

Duplication means YOU are responsible for also updating the name parameter in the friends table, when you change the name in the user table. If you do not do that consistently, then your data becomes inconsistent and users start seeing "strange things happening."

(2) Redundancy

In case of redundancy, you do not yourself have two copies of any piece of data. The database can though. This is useful for safety. For example, you can place your database on two separate computers. If one computer goes down, the same data is available on the other computer.

Redundancy is definitively a copy, but the access to either version of the data is 1 to 1 exactly the same to you. The RDBM may choose one or the other based on different parameters such as how fast or whether the other computer network connection is down.

The RDBM manages the copies in very consistent ways, the same problem I just mentioned about user names. If the RDBM is asked to update the user name, it has to update all the versions (all copies). The good thing is it does not have to do it at the time you do the INSERT or UPDATE, it can delay the update on the copy computer. So... the implementers of the database do exactly the opposite of what they were told and make duplicates of the data! (which is probably the main source of confusion in this realm.)

You may want to read about Cassandra for fun things in that respect. Cassandra is the type of database used by Google which update data in a ring of computers with possibly heavy redundancy.

(3) Even More Duplication

Since you asked, I'll also let you know that RDBMs make use of what we call Indexes... And Indexes duplicate your data further. To continue with the user example, the name of the user could be used in an index. The Index would then look like this, for example:

Alexis
Henri
Margarette
Phyllis
...

When you do an UPDATE of the users table, the RDBM needs to also update its index and move the name to the new position, and all of that with maintaining consistency (SQL warrants that the data is always 100% consistent...)

So, renaming Alexis as Tristan would give a new index:

Henri
Margarette
Phyllis
Tristan
...

And here we see that the duplication is actually necessary. And if you see how you can handle that properly, without ever losing any existing data, you understand why duplication is difficult to deal with.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.