I'm in the process of redesigning a database that has an unique requirement. The following is simplified example of one of the table design in the current database:

Mailed N-10
Area C-5
Listcode C-2
Package C-5
nArea C-5
nListcode C-2
nPackage C-5

As you can see, there are basically three fields and another set of same fields proceeded with "n" character to represent "new". Any changes to the original data are saved only to these "n" fields so that all original data are preserved. The reason that the tables are designed like this is so that the use can see what the "original" data was before changes were made and revert any changes if necessary which you can't do if you override the original data.

When the reports are run, the query looks like the following:

SELECT SUM(Mailed) AS Mailed,
Area = CASE WHEN nArea<>'' THEN nArea ELSE Area END,
Listcode = CASE WHEN nListcode<>'' THEN nListcode ELSE Listcode END,
Package = CASE WHEN nPackage<>'' THEN nPackage ELSE Package END
FROM Mailed...

Now, finally to my question: since most records don't have any new values assigned, I feel this design isn't optimal but I can't seem to find a good replacement design. Any thoughs?