Temporal database design, with a twist (live vs draft rows)

by

Anchor modeling is a nice way to implement a temporal dB — see the Wikipedia article too.
Takes some time to get used to, but work nice.
There is an online modeling tool and if you load the supplied XML file [File -> Load Model from Local File]
you should see something like this — also use [Layout --> Togle Names].

enter image description here

The [Generate --> SQL Code] will produce DDL for tables, views and point-in-time functions.
The code is quite long, so I am not posting it here. Check the code out — you may need to modify it
for your DB.

Here is the file to load into modeling tool.

<schema>
<knot mnemonic="EXP" descriptor="Expired" identity="smallint" dataRange="char(1)">
<identity generator="true"/>
<layout x="713.96" y="511.22" fixed="true"/>
</knot>
<anchor mnemonic="US" descriptor="User" identity="int">
<identity generator="true"/>
<attribute mnemonic="USN" descriptor="UserName" dataRange="varchar(32)">
<layout x="923.38" y="206.54" fixed="true"/>
</attribute>
<layout x="891.00" y="242.00" fixed="true"/>
</anchor>
<anchor mnemonic="PO" descriptor="Post" identity="int">
<identity generator="true"/>
<attribute mnemonic="TIT" descriptor="Title" dataRange="varchar(2)">
<layout x="828.00" y="562.00" fixed="true"/>
</attribute>
<layout x="855.00" y="471.00" fixed="true"/>
</anchor>
<anchor mnemonic="TG" descriptor="Tag" identity="int">
<identity generator="true"/>
<attribute mnemonic="TGT" descriptor="TagText" dataRange="varchar(32)">
<layout x="551.26" y="331.69" fixed="true"/>
</attribute>
<layout x="637.29" y="263.43" fixed="true"/>
</anchor>
<anchor mnemonic="BO" descriptor="Body" identity="int">
<identity generator="true"/>
<attribute mnemonic="BOT" descriptor="BodyText" dataRange="varchar(max)">
<layout x="1161.00" y="491.00" fixed="true"/>
</attribute>
<layout x="1052.00" y="465.00" fixed="true"/>
</anchor>
<tie timeRange="datetime">
<anchorRole role="IsTagged" type="PO" identifier="true"/>
<anchorRole role="IsAttached" type="TG" identifier="true"/>
<anchorRole role="BYAuthor" type="US" identifier="false"/>
<knotRole role="Until" type="EXP" identifier="false"/>
<layout x="722.00" y="397.00" fixed="true"/>
</tie>
<tie timeRange="datetime">
<anchorRole role="Contains" type="PO" identifier="true"/>
<anchorRole role="ContainedIn" type="BO" identifier="false"/>
<layout x="975.00" y="576.00" fixed="true"/>
</tie>
<tie>
<anchorRole role="CreatedBy" type="TG" identifier="true"/>
<anchorRole role="Author" type="US" identifier="false"/>
<layout x="755.10" y="195.17" fixed="true"/>
</tie>
<tie>
<anchorRole role="CreatedBy" type="PO" identifier="true"/>
<anchorRole role="Author" type="US" identifier="false"/>
<layout x="890.69" y="369.09" fixed="true"/>
</tie>
<tie>
<anchorRole role="ModifiedBy" type="BO" identifier="true"/>
<anchorRole role="Author" type="US" identifier="false"/>
<layout x="1061.81" y="322.34" fixed="true"/>
</tie>
</schema>

More Related Contents:

  1. How to implement a many-to-many relationship in PostgreSQL?
  2. Use email address as primary key?
  3. Refactor a PL/pgSQL function to return the output of various SELECT queries
  4. How to check if a table exists in a given schema
  5. How to update selected rows with values from a CSV file in Postgres?
  6. How to create multiple one to one’s
  7. Strings as Primary Keys in SQL Database [closed]
  8. Custom SERIAL / autoincrement per group of values
  9. Safely rename tables using serial primary key columns
  10. single fixed table with multiple columns vs flexible abstract tables
  11. Primary key or Unique index?
  12. How can I send email from PostgreSQL trigger?
  13. How to design a database for User Defined Fields?
  14. Composite Primary key vs additional “ID” column?
  15. Query that ignore the spaces
  16. How to find a table having a specific column in postgresql
  17. Postgres constraint for unique datetime range
  18. INSERT COMMAND :: ERROR: column “value” does not exist
  19. MySQL – how to front pad zip code with “0”?
  20. Bulk/batch update/upsert in PostgreSQL
  21. Postgres Error: More than one row returned by a subquery used as an expression
  22. Grant privileges for a particular database in PostgreSQL
  23. SQL: Using NULL values vs. default values
  24. How to compare dates in datetime fields in Postgresql?
  25. What is the best way to implement soft deletion?
  26. PostgreSQL how to create a copy of a database or schema?
  27. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  28. Historical / auditable database
  29. Postgres: convert single row to multiple rows (unpivot)
  30. Single or multiple databases

Leave a Comment