Designing a database that supports versioning of its data?
Date: Thu, 19 Dec 2002 14:30:52 +0200
Message-ID: <3e01bbff$0$228_at_hades.is.co.za>
Hi all,
I would like some suggestions and ideas regarding the design of a database that supports versioning of its information. Firstly let me explain what the database needs to represent - comments on this design are also welcome so long as they in regard to versioning :-)
If there is somewhere else I could post this question, please could you let me know - the more comments I get the better :)
So, on with the questions:
I need to represent a network. The network is made up of Nodes and Connections. Connections link two nodes. A node may be connected to any number of other nodes via a single connection.
Naturally, I have Connection and Node tables. To represent the connection of two Nodes, I also have a NodeLink table. This table has a Connection FK and a Node FK. So, for a single Connection, there will be two entries in the NodeLink table - both have the same Connection_FK but each with a different Node_FK.
There are various types of connections, each having their own configuration information. To represent this, there are two additional tables - Physical and Virtual. Both these tables have a Connection FK. It can be said that the type of Connection determines which of these two tables will contain an entry for that connection. If one table does not contain a reference, one is gaurenteed to a reference in the other table. Conversely, if a reference is found in one table, a reference will not exist in the other table.
So we have something along the following lines in terms of an ERD; the numbers indicate the min/max cardinality of each relationship.
[Virtual]
0,1
|
1,1
[Connection] (1,1)---(2,2) [NodeLink] (1,*)---(1,1) [Node]
1,1
|
0,1
[Physical]
So representing the following layout would be rather simple:
A --- B --- C
|
D --- E
Say I then reconfigure the network to look as follows:
B --- C
|
A --- D --- E
I need to keep the original layout in my database as well as represent the new "proposed" layout. So it could be said there are two versions of this network. An OLD one and a ACTIVE one. With this simple requirement, what would be the recommended schema change for the database so that it can support this versioning?
With that in mind, I also have the following requirements:
- The layout of the network may not change, but the configuration
NOTE: Duplicating the structure of the entire network is not an option as the network could be huge.
Thanks, Andrew Received on Thu Dec 19 2002 - 13:30:52 CET