Re: Temporal Databases (Database design questions)

On 2008-06-18 09:41, David wrote:

Question 3: Temporal databases

http://en.wikipedia.org/wiki/Temporal_database

I haven't used them before, but I like the idea of never
deleting/updating records so you have a complete history (a bit like
source code version control).

How well do temporal databases work? Do RDBMS (like Postgresql) need
add-ons to make it effective, or can you just add extra temporal
columns to all your tables and add them to your app queries? Does this
increase app complexity and increase server load a lot?

Are there Python libraries which simplify this? (eg: add-ons for
Elixir or SQLAlchemy).

Or should apps all implement their own 'temporal data access' module,
which transparently uses the current date & time until queried for
historical data?

You can have complete history in a database schema by:

* adding a version column
* adding a modification timestamp (and modification username,
if that's relevant for you)
* updating the version upon INSERT and UPDATE
* have a history table for each "live" table that gets
filled using a trigger on the version column which moves
the inserted/updated/deleted rows into the history table
* the history table will have to have an additional column
for storing the method of how the row got into the table
(ie. insert/update/delete)

The main app will only use the "live" tables with the current
data. An audit tool would then provide access to the history
tables.

This works for all databases that have triggers, even SQLite.

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source (#1, Jun 18 2008)

>>Python/Zope Consulting and Support ... http://www.egenix.com/
mxODBC.Zope.D atabase.Adapter ... http://zope.egenix.com/
mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/

_______________ _______________ _______________ _______________ ____________
2008-07-07: EuroPython 2008, Vilnius, Lithuania 18 days to go

:::: Try mxODBC.Zope.DA for Windows,Linux,S olaris,MacOSX for free ! ::::
eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611

Jun 27 '08 #1

Subscribe Reply

939

Similar topics

2186

Vote on comp.databases.postgresql

by: Otis Green | last post by:

Vote for or against a new newsgroup proposal. To summarize what you need to do, just send an empty e-mail to postgresql-ballot@netagw.com You will receive a ballot by e-mail. Follow the instructions and vote. _______________________________________________________________________ FIRST CALL FOR VOTES (of 2)

PHP

478

RFD: comp.databases.mysql

by: Gary L. Burnore | last post by:

REQUEST FOR DISCUSSION (RFD) unmoderated group comp.databases.mysql This is an invitation to discuss the following proposal to create newsgroup comp.databases.mysql. Please note that YOU CANNOT VOTE NOW; you may be able to vote on a version of this proposal later. See the PROCEDURE section below if you need information about how the discussion works. PLEASE POST ANY FOLLOWUPS TO THE NEWSGROUP NEWS.GROUPS.

PHP

1221

2nd CFV: comp.databases.etl

by: Cara | last post by:

2nd CFV: comp.databases.etl Attention, all data warehouse folks! There is a Call For Votes for a new newsgroup about ETL issues. If you are interested, please vote YES by December 12, 2003. Contact me with any questions at a5661991@yahoo.com LAST CALL FOR VOTES (of 2) unmoderated group comp.databases.etl Newsgroups line: comp.databases.etl Extraction, transformation, loading issues.

.NET Framework

1837

OOD/OOP and databases

by: Thomas Jespersen | last post by:

Hello I've been reading a lot of great OOD/OOP books lately (e.g.. Martin Fowlers UML Distilled, Patterns of Enterprise Application Architecture, Refactoring, Kent Becks's Test Driven Development, and also a book called Design Patterns Explained.)... al of them with the focus on Java, because I think the best books are targeted Java. But the all fit very well to the .NET platform. I have one big problem when reading these great books,...

.NET Framework

2266

Has anyone tried Date/Darwen/Lorentzos's model for temporal data?

by: Eric D. Nielsen | last post by:

I'm in the process of adding more historic information to one of my databases. I've liked the theoretical treatment of the concept in "Temporal Data and the Relational Model", by Date, Darwen, & Lorentzos. A lot of it is not realizable without a lot of user defined types/functions/etc. I was wondering if anyone else has tried to use their approach as a base for their historical databases in PostGreSQL and has any "lessons learned" to...

PostgreSQL Database

14276

One Database vs. Multiple Databases

by: TC | last post by:

I need to design a system which represents multiple "projects" in SQL Server. Each project has the same data model, but is independent of all others. My inclination is to use one database to store all projects. Looking at the numbers involved, however, I wonder if I would get better performance by storing each project in its own database. Suppose I have 50 projects, each with two users and 10,000 rows; it seems to me I'd rather have 50 x...

Microsoft SQL Server

4566

When to make separate databases?

by: LurfysMa | last post by:

I would like to hear opinions on the tradeoffs of putting the tables, forms, and queries for several related datasets in separate databases vs one combined database. I am working on an application that will have a number of "subjects". The subjects have a number of commonalities, but are not identical. I am hoping that I will discover that the subjects fall into a few "types" and a common database structure can be used for each type. ...

Microsoft Access / VBA

2763

Database connection through SP

by: situ | last post by:

Hello all, i have Database1 and database2, is it possible to make database connection to database2 by running stored procedure on database1. Thanks and Regards Situ

DB2 Database

1398

Career questions: databases

by: dreamznatcher | last post by:

Hello, Not sure whether this is the right group to ask this, but there are so many groups out there I got kinda lost! Please help me out on this career dilemma I'm going through, or kindly forward me to an appropriate group. I am a graduate in Computer Engineering, and am particularly interested in databases. I can chalk out databases in detail on paper and I'm also good at visualizing the development phases of a potential application....

PHP

1238

Re: Temporal Databases (Database design questions)

by: David | last post by:

On Wed, Jun 18, 2008 at 11:16 AM, M.-A. Lemburg <mal@egenix.comwrote: Thanks for your reply. How do you maintain foreign key references with this approach? eg, you have these 4 tables: table1 - id

Python

9563

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

10144

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

9997

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

9937

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

6642

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5270

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

3917

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

3522

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

2793

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General