Should you ever duplicate data? [closed]

Question

This is a very general question about database design in regards to products that have multiple running campaigns that may share data sets. I'm trying to understand concepts on why I should and should not do something like this.

I was thinking of having a raw set of data, and then copying a part of that set over to the campaign so that the campaign will always have historical data. For example, even if the raw data updates, the campaign's data wouldn't change. However, the issue is that there is just so much duplication and I'm not sure if that's a very good design. Any insight appreciated.

Answer 1

Actually, this is a great question. Database design for transactional or OLTP systems does seek to eliminate storing the same information in more than one place.

That said, storing historical values does not violate data redundancy. You are actually storing a value that is different from your normal transactional data.

For instance, let's say you have a sales region associated with a particular customer on the customer table. When you capture a Sale, you may wish to store the region on the Sales Order Header table. This is not necessarily duplication of data, but rather good design in the case where Sales Regions may change. In this case, you may want to capture the region that applied to the order at the time of the order.

Tomorrow, the customer's region may change. And you will be able to create reports based upon the historically correct region.