Version Capability

[dmontano]

How does one go about creating a versioning mechanism for records in a table?

 

For example:

Invoices can have version,

Estimates can have versions, 

Quotes can have versions, etc.

 

While my particular case is not about any of those three types of entities, I do have a need that requires versioning.

 

I thought about the serial increment, but am clueless on how to go about using it.

 

My table is of SCHEMEs

A SCHEME must record the person who created it;

A SCHEME must have a SCHEME NAME - does not have to be unique because of the need to "version" it.

A SCHEME should have a VERSION mechanism, like Version 1, Version 2, etc.

 

The idea is that John Doe should be able to create a SCHEME record and name it PRODUCT CLASSIFICATION SCHEME. I believe right off the bat it should automatically receive a VERSION title of: "VERSION 1".

 

If John Doe wants to create an additional version (using the same SCHEME name of PRODUCT CLASSIFICATION SCHEME, he should be able to do so, but have it automatically incremented as VERSION 2. Somehow, the creation of the second version record needs to know that a first version record exists.

 

How can I incorporate this feature?

[Dave Graham]

There are many ways to implement this feature. I would recommend that you will duplicate the parent record along with all of the related child records each time you want to add a new version. Old versions should be locked from editing to prevent accidentally changing old values. You can optionally add a related table that will keep track of the versions so you can see all related versions, along with a comment as to why it was revved. 

[dmontano]

Hi David,

 

I agree. I have been thinking about this exhaustively over the weekend and still do not have a versioning mechanism in action. I can not figure out the "version" field mechanism to instantiate a version number.

 

SCHEME TABLE KEYS (What I have)

Field: Scheme ID (Primary Key for Scheme table)

Field: Scheme Name (Foreign Key from related table TERMs)

Field: Data Provider (Foreign Key from Related table DATA PROVIDER)

 

A Scheme must have a Scheme Name (Does NOT have to be unique, otherwise versions of that SCHEMA NAME would be impossible).

A Scheme Name can be used for one or more Schemes (This allows a DATA PROVIDER to provide multiple VERSIONs of FRUIT SCHEMEs, for example)

A Scheme must be created or provided by one Data Provider (Must know who provided the scheme)

A Data Provider can create or provide one or more Schemes (This allows a DATA PROVIDER to provide FRUIT SCHEME 1, FRUIT SCHEME 2, VEGETABLE SCHEME 1, etc.)

 

Sample records:

SCHEME PK, DATA PROVIDER FK, SCHEME NAME FK, SCHEME NAME VERSION, DATA PROVIDER SCHEME VERSION

 

Scheme Id 001, John Doe (FK), Fruit Scheme (FK), Scheme Name Version 1, Data Provider Scheme Name Version 1

Scheme Id 002, John Doe (FK), Fruit Scheme (FK), Scheme Name Version 2, Data Provider Scheme Name Version 2

Scheme Id 003, Sally Sue (FK), Fruit Scheme (FK), Scheme Name Version 3, Data Provider Scheme Name Version 1

Scheme Id 004, Billy Bob (FK), Fruit Scheme (FK), Scheme Name Version 4, Data Provider Scheme Name Version 1

Scheme Id 005, John Doe (FK), Vegetable Scheme (FK), Scheme Name Version 1, Data Provider Scheme Name Version 1

Scheme Id 006, Sally Sue (FK), Vegetable Scheme (FK), Scheme Name Version 2, Data Provider Scheme Name Version 1

Scheme Id 007, Sally Sue (FK), Vegetable Scheme (FK), Scheme Name Version 3, Data Provider Scheme Name Version 2

 

The above shows:

There are 4 versions of Fruit Schemes;

Of the 4 Fruit Scheme Versions — John Doe provided 2 versions, Sally Sue provided 1 Version, and Billy Bob provided 1 version

 

and 

 

There are 3 versions of Vegetable Schemes;

Of the 3 Vegetable Scheme Versions — John Doe provided 1 version, Sally Sue provided 2 Versions

 

I am thinking I need a calculated field that creates a "1" the first time a unique SCHEME NAME (FK) is instantiated as a record in SCHEME table. This would designate that SCHEME NAME as version 1. The next time a data provider needs to create a version of the same SCHEME Name, the calculation field would count existing records of that same SCHEME Name and its version and auto-increment the version field by 1 and place in the version field (version 2). The version number though can not be modified later (recalculated) as that would change the meaning of the version mechanism from a VERSION, to a numbered instance in the table.

 

Next, I need to extend the same concept of version of Scheme Name to the Data Provider AND Scheme Name to indicate how many versions a Data Provider has provided of a Scheme Name.

 

I do hope someone can chime in. Even if it is just the mechanism for version on the scheme name I think I could extend the approach to the data provider/scheme name.

 

Thanks in advance!