I am considering designing a relational DB schema for a DB that never actually deletes anything (sets a deleted flag or something).

1) What metadata columns are typically used to accomodate such an architecture? Obviously a boolean flag for IsDeleted can be set. Or maybe just a timestamp in a Deleted column works better, or possibly both. I'm not sure which method will cause me more problems in the long run.

2) How are updates typically handled in such architectures? If you mark the old value as deleted and insert a new one, you will run into PK unique constraint issues (e.g. if you have PK column id, then the new row must have the same id as the one you just marked as invalid, or else all of your foreign keys in other tables for that id will be rendered useless).

Here are some additional questions that you'll also want to consider

1. How often do deletes occur. What's your performance budget like? This can affect your choices. The answer to your design will be different depending of if a user deleting a single row (like lets say an answer on a Q&A site vs deleting records on an hourly basis from a feed)

2. How are you going to expose the deleted records in your system. Is it only through administrative purposes or can any user see deleted records. This makes a difference because you'll probably need to come up with a filtering mechanism depending on the user.

3. How will foreign key constraints work. Can one table reference another table where there's a deleted record?

4. When you add or alter existing tables what happens to the deleted records?

Typically the systems that care a lot about audit use tables as Steve Prentice mentioned. It often has every field from the original table with all the constraints turned off. It often will have a action field to track updates vs deletes, and include a date/timestamp of the change along with the user.

For an example see the PostHistory Table at http://data.stackexchange.com/stackoverflow/query/new

I want to have compatible SQL for both Oracle database and Microsoft SQL server.

I want a compatible SQL expression that will return true for not null and not empty strings.

If I use:

column <> ''

it will work on Microsoft SQL server but not on Oracle database (as '' is null for Oracle)

If I use:

len(column) > 0

it will work on Microsoft SQL server but not on Oracle database (since it uses length() )

NULLIF is available on both Oracle (doc) and SQL Server (doc). This expression should work:

NULLIF(column, '') IS NOT NULL

In both servers, if column is NULL, then the output of NULLIF will just pass the NULL value through. On SQL Server, '' = '', so the output of NULLIF will be NULL. On Oracle, '' is already NULL, so it gets passed through.

This is my test on SQL Server 2008 R2 Express:

WITH SampleData AS
    (SELECT 1 AS col1, CAST(NULL AS varchar(10)) AS col2
     UNION ALL
     SELECT 2, ''
     UNION ALL
     SELECT 3, 'hello')
SELECT *
  FROM SampleData
 WHERE NULLIF(col2, '') IS NOT NULL;

And this is my test case on Oracle 10g XE:

WITH SampleData AS
    (SELECT 1 AS col1, NULL AS col2 FROM DUAL
     UNION ALL
     SELECT 2, '' FROM DUAL
     UNION ALL
     SELECT 3, 'hello' FROM DUAL)
SELECT *
  FROM SampleData
 WHERE NULLIF(col2, '') IS NOT NULL;

Both return 3 as expected.