Database Modeling Question

Question

I am having a doubt while creating a DB, and I would like to know if there are objective arguments that could help me mak a better decision.

Case

A project that can have several revisions, so I created entity Project and Project_Rev. Each revision has positions, so I have created entity Position. Each position has elements, so I created entity Element. Until this point everything is super normal, from Project to Element entity by entity, is a one-to-many relationship, has you can see in the below graphic:

The issue is the following: Project_Rev increment and Elements increment, but there is no logical association! I might have:

Project1_Rev1 -> Position1 -> Element1_Rev1, Element2_Rev1 Project1_Rev2 -> Position1 -> Element1_Rev4, Element2_Rev2 Project1_Rev3 -> Position1 -> Element1_Rev5, Element2_Rev2 Project1_Rev4 -> Position1 -> Element1_Rev5, Element2_Rev10 

For this reason, I thought in two solutions:

Solution 1

To connect Project_Rev with Element, like the image below:

This solution works, but it doesn't seem the right one because there is redundancy, same element will be referenced twice in the database for the same purpose!

Solution 2

I thought in creating a middle ground table for Project_Rev, Position, Element, where for each Project_Rev and Position combination there is a List of Elements. Like you can see in the image below:

The Above image is not correct, the below is:

This one seems more correct to me, but because of the two one-to-one connections "tastes" like a view to me! Nevertheless seems more correct, there is no redundancy like in solution 1 mentioned above!

What is your advice about this, should I use solution 1, solution 2 or other that I am not mentioning?

Answer 1

Solution 1 has the advantage of reflecting perfectly a flexible versioning scheme. Suppose you create a new revision of the project:

• you could reorganize the positions (subdividing a former position in two sub-positions or group two positions in a common one).
• you could update position attributes (after all, it's a new revision, so the infos could be changed).
• you could add or remove elements
• you could change elements (for example different workload estimates for the elements)

Of course the drawback is that the unchanged elements or positions are just duplicated. That's the cost of the flexibility and simplicity.

If you want to avoid redundancy, you'd need an alogrithm that works with deltas: only the changed elements are created in the revision. But then you'd need a sophisticated algorithm to read the history and put the pieces together.

Solution 2 seems inconsistent. The one to one relations between Project_Rev and Project_Rev_Position_Element and then Project_Rev_Position_Element and Position means that there's only one position per revision. Is it really what you want ?

Additional solution: If Elements are potentially shared between several projects, you could opt for a many to many relationship. The many-to-many requires an intermediary table Element_in_Position:

 Project -< Project_Rev -< Position -< Element_in_Position >- Element 

With this alternative, two different revisions could share the same elements if these are unchanged, even if they are put in a different position. If it turns out that Element has to be revised in one revision, then you'd have to create a clone and change the clone.

The advantage is that you avoid redundancy of Element (you could still have it for Position though) and have a fully flexible versioning. The only difficultly is when Element is edited: you have in your code to identify the user's intent: change invariant attributes that have to be shared between all the versions ? change attributes specific to a specific revision of an Element that is not shared with any other revision ? Or cloning a shared Element so that attribute changes can be limited to only one revision.

Answer 2

Graphics from solution 1 and solution 2 in the question are incorrect, because, and mentioned in the examples combinations in the question, ProjectRev and Position are also a many-to-many connection.

Combinations:

Project1_Rev1 -> Position1 -> Element1_Rev1, Element2_Rev1 Project1_Rev2 -> Position1 -> Element1_Rev4, Element2_Rev2 Project1_Rev3 -> Position1 -> Element1_Rev5, Element2_Rev2 Project1_Rev4 -> Position1 -> Element1_Rev5, Element2_Rev10 

After a lot of time thinking about this, the solution I found is the following:

Basically, for each combination of Entities ProjectRev and Position, I will have a List of Elements. With the mentioned, I can make all the needed combinations and keep track of all history of the combinations.