database-designarchitectureversioningapplication-design

What is the purpose (or usefulness) of a data/application "version" table in the database?

This is a design question.

What is the purpose or usefulness of having a "Version" table in the database that stores the freshness date and a version of the database and/or application(s) that connect to it.

For example, the following DDL...

CREATE TABLE
  [dbo].[Version]
  (
    FreshnessDate DATETIME,
    DatabaseVersion VARCHAR(10),
    ApplicationVersion VARCHAR(10)
  )

...creates a table that stores some basic versioning information. On my team, the argument is that we don't need this because everyone develops (even database development) from the same shared database on the network (we aren't allowed to have individual databases - even empty ones - on our development boxes).

The additional argument against having this type of versioning table is that it doesn't benefit our process: We develop on the development database, which is then used to generate a single, huge script, from which differences are determined and an update script is generated (migration is done by hand). This update script is then run on the QA shared database, and after it passes QA, the same script is then run on the Production database. Once every quarter, the production database is pulled down to both QA and Development database servers to synchronize the data to help with debugging.

Note that our department is run by a CFO who dictates the process requirements. This question is specifically on whether or not anyone else sees an advantages to the Version table or if it really is unnecessary. Since someone on our team suggested that it was unnecessary because of our process, I added our process in this post.

I personally have arguments for it, but want some council and feedback on if adding such a table is a case of over engineering.

Thanks!

Solution

  • On our side we got such table and we have automatic migration based on that table - schema updates, schema migrations. We got on additional field- reference to file. So in our workflow is not possible to exist without such table. Table data is maintain also automatically.

    In your case pros for such table is that you get application version and db version stored historically so theoretically is possible to gain some additional information when you debug issues.

    I rather remove it based on your workflow.