Table Design

[cable]

I'm being asked to update a existing set of databases, and as the current structure isn't at all very good I can start from scratch.

It is a audit type application and the way the current system works is that there is a database for unapproved and a database for approved, and all though the item's are moved from one database to the other their associated key is changed. ie its been made very clear which set they belong to.

This will be merged into one database but whats the best way to do this:
If I have just one table with a status field for the approval status, I would have to change the id, which would mean an ID that the user uses and an internal one used in the tables.

Or I could have two tables and move the data once it gets approved.

Whats the advantages/disadvantages either way?

 

[FoFa]

Interesting concept. I would lean towards two tables at least. A main table for the common information, and a second table for the status and unique information for each status. That way you have the visability of both the unapproved and approved status for each audit (or history). And yes use an internal key to tie things together.