I have a table, call it `P`, that currently has a series of records, each with a unique key:

```
Key1 value1
Key2 value2
Key3 value3

```

and so on, where each value is actually a set of columns.

I now have a requirement to make these records mutable, and to record the history of changes as an audit log.

I am thinking the best way to do this is to modify `P` so that it is

```
Key1 Rev1 value1
Key1 Rev2 value1a
Key2 Rev1 value2
Key2 Rev2 value2a

```

and so on. The most current record for any given key would always have the highest revision marker.

The problem is, this ruins comparability for queries that expect only current records. I am thinking of renaming `P` to something like `PComplete` and creating a view `P` that would only select the highest revision marker for each key, and omit the revision marker, thus emulating the original table `P`.

As I am not primarily a SQL expert:

Is this a solid technique?
What is the best way to design the new view `P` so that it selects the most current (call it greatest) row for each present key in the base view `PBase`?

Site pointers are most welcome; I have no idea what terms to Google to find best practice methods for this type of solution.

Note: this is Microsoft SQL Server 2008 R2.