Re: Newbie question on table design.


I can easily think of 2 ways to support record [sic] un-/deletion.

1. Add an 'is_deleted' column that can take a binary 1/0 value (1 =
deleted, 0 = not-deleted). .. <<

Rows are not records. Among the MANY differences is that rows are
shared and have transaction control problems. Records in a single
application file system do not. So you probably will want to have
flags that show (delete_status, commit_status). That would allow the
engine to support uncommitted reads and the other transaction levels.
Then there is the need for a housekeeping routine to physically remove
the (delete, commit) rows, fix the indexes and re-do the stats.

Oh, then that delete_status flag needs to work with updates and
inserts who also have those transaction levels to consider, so it is
probably going to be several bits. Database internals are harder than
file systems.

.

Relevant Pages