General versioning strategy for selecting table data in a highly normalized database

Question

Sorry for the long headline, but the requirement / problem is quite specific.

With reference to the following sample (but very simplified) structure (in psuedo SQL), I hope to explain it a little better.

TABLE StructureName {
  Id GUID PK,
  Name varchar(50) NOT NULL
}

TABLE Structure {
  Id GUID PK,
  ParentId GUID,                 -- FK to Structure
  NameId GUID NOT NULL           -- FK to StructureName
}

TABLE Something {
  Id GUID PK,
  RootStructureId GUID NOT NULL  -- FK to Structure
}

As you can see, the structure is a simple tree structure (do not worry about ordering the children for the problem). StructureName is a simplification of the translation system. Finally, “Something” is just something that refers to the root structure of the tree.

This is just one of many tables that should be versions, but this example serves as a good example for most cases.

/ "". .

, , , , "" . , , "-", , .

- , Wiki , .

, .

.

leppie

Answer 1

:

: , version_id PK FK; , .

• pro:
• con: ( )

:. , , valid_from/valid_to.

• pro:
• con: ,

: . , , .

• pro: , .
• con: , .

: :

id,  timestamp,  changed_table,  changed_column,  old_value,  new_value,  changed_by

:

transaction,  table_change,  changed_column

• pro: ,
• con: , .

, : .

Answer 2

" ".

, , .

, Kimball .

• . , , . , , .

• . , ; . , , -, , . , "", .

• . , . , , .