===========================
tbl_entries
===========================
id int (PK)
created_date date
created_by int (FK)
default_version int
is_deleted boolean
===========================
tbl_entries_versions
===========================
entry_id int (FK)
version int
created_by int (FK)
created_date datetime
===========================
tbl_entries_data_1
===========================
entry_id int (FK)
value varchar
version int
language varchar
To get an entry, first check that it is not deleted:
$entry = SELECT default_version FROM tbl_entries WHERE id=1 AND is_deleted=FALSE;
Then you know what version this entry is presently on, so you can get the data for this version:
SELECT value FROM tbl_entries_data_1 WHERE entry_id=1 AND version=$entry->default_version;
This allows you to create a new version of an entry, but leave the "default_version" on the previous version, thereby creating a draft (or "unpublished"). This feature can lie dormant (i.e. default_version is always updated when creating a new version) until an extension chooses to expose the concept of unpublished versions.
No data is ever deleted. When an entry is "deleted" the is_deleted flag is set to TRUE and these entries are subsequently removed from the UI. However an audit trail (such as Tracker or Audit Trail) extension would expose these purge actions and could permit an entry to be recovered by reversing the flag to TRUE.
Moderation can occur in two ways. Moderation of user generated content would be done as it is normally done presently: add a select box for Pending, Declined and Approved states, and filter this in a data source. However internal moderation (one editor creates an entry, needs another editor to approve it) would be a new custom field type. This field type would expose a select box as before (pending, declined, approved) to filter on, but when someone saves an entry as Approved, the default_version column in tbl_entries is updated to reflect that approval. In this way, content editors would be able to create multiple drafts of an entry (which have a Pending state, so are filtered out by the DS), and the action of "publishing" it changes the select box to Approved, and also marks that version as the default.
The same system can be used for languages. When creating a new section (or even at the field level) the developer marks which languages an entry can be created under. When saving an entry, the field data is stored in a row along with the new version number, but also the corresponding language. This means you have version controlled multilingual content in the core.
Exposing the ability to translate in the UI is another challenge entirely, but this simple database structure would permit each field to be translated individually, or the entire entry data to be translated. It'd also allow one entry to be created only in language X and another entry only in language Y — it's the UI that governs this. This generic column structure should permit all variations.
Having these in the core would allow for extra "system" fields in data sources. For example if you wanted to get the French version #4 of entry #1, your DS filters:
Filter: System ID
Value: 1
Filter: System Version
Value: 4
Filter: System Language
Value: fr