0

I have an entity consisting of some fix attributes and some variable attributes which should be versioned. This leads to a database design having a table for the fix parts (Table F) and a table for the versioned parts (Table V).

V has a many to one relation to F (one entity many have many versions). F has a one to one relation to one V to indicate the current version. This leads to circular dependencies in our database making life hard.

We prefer a solution ensuring data consitency on database level (so setting a currentversoin-flag on V is no solution, since we can't ensure there are not multiple current versions in case of failure). Do you see any better way to modell this without circles?

mibutec
  • 2,929
  • 6
  • 27
  • 39
  • Only a pitiful number of constraints can be declaratively enforced by SQL DBMSs. In fact, there's no reason they couldn't allow cycles in FK constraints. So then you need to move to triggers. For one approach to sound trigger-implemented declaratively-specified arbitrary constraints, see Applied Mathematics for Database Professionals by deHaan & Koppelaars. – philipxy Nov 03 '16 at 12:06
  • If you are so concerned about consistency, why are you storing redundant data? If the most recent version is a function of an entity's versions, like the max, to avoid redundancy you need to put that criterion in your query joining F & V. Otherwise, the version number should be part of a FK in V to F. PS It would probably be helpful for you to give an example including DDL & data. PS I expect your question is a duplicate, keep searching. – philipxy Nov 03 '16 at 12:12
  • I suspect that somewhere there exists a training course that tells students that it's really really bad to have *potential* joins in their schema that form loops. In this case, I don't see a problem. One alternative would be to dynamically calculate the most recent version, which would be pretty cheap. Can you explain what the problem is here? – David Aldridge Nov 06 '16 at 10:13

1 Answers1

0

Remove the reference in F to the current version in V. It's simply not needed. The current version is the latest version.

Here are some "must have" features of a versioning design:

  • One and only one date/time value (no FROM/TO date pairs)
  • No separate field used to indicate the current version.
  • Creating a new version involves writing one record. No updating of other versions.
  • Foreign key references work the same as always.
  • Use a standard pattern that is consistent and already well known (in this case, the pattern is 2nf).

Here is an answer with more details. It also contains links to even more details if you think it holds promise.

Community
  • 1
  • 1
TommCatt
  • 5,498
  • 1
  • 13
  • 20