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.


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: enter image description here

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:

enter image description here

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:

enter image description here

The Above image is not correct, the below is:

enter image description here

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?

  • Try using a ternary relationship. (also composite keys in relational db.) – Erik Eidt Dec 10 '17 at 16:17
  • When you say "doubt", do you mean "question"? – Walter Mitty Dec 11 '17 at 15:49
  • @WalterMitty yes, i meant question! – f4d0 Dec 12 '17 at 1:43
  • Create a database view to give you solution 1 when reading data, and keep your existing model when writing data. Or use an ORM and do a join to get the info. – Greg Burghardt Dec 14 '17 at 22:47

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.

  • Hi @Christophe, first of all thanks for your complete answer and for the effort! You are right, solution 2 is not correct, it does not make sense the Graphic, I will edit it, so you can see what I mean! I believe is a Ternary relation. Like Erik Eidt mentioned in one comment. Regarding your solution, I don't think it solves my situation, because I cannot connect the revision of each Element with the Project_Rev. Most of the times, the Positions will be the same across all the Project_Rev. – f4d0 Dec 10 '17 at 21:49
  • For example, If I have a total number of 10 Project_Rev, I don't know what is the revision of Element 3 inside Position 2 of Project_Rev number 5. Most probably Position 2 will exist since Project_Rev 1, but the Element 3 might have 13 revisions across all the 10 Project_Rev. Inside a Project_Rev an Element might not be changed as it can be changed N times. – f4d0 Dec 10 '17 at 21:56
  • @Nuno it all depends of what you're representing. WHat you're describing here is that Elements have a versioning that is independent of the project versioning (e.g. Element X could have version 13 but Element Y could be at version 2, which is totaly unrelated to Element X version 2). In this case I'd suggest to manage Element and an independent Element_version and that the combination of the two is used in a preoject version. THe many-to-many table would then in effet implement a ternary relation. – Christophe Dec 10 '17 at 22:02
  • I think I explained myself incorrectly! I sad that there is no Logical association between Project_Rev and Element when it comes to the revision number! But I need to know the revision of each Element in each Project_Rev. – f4d0 Dec 10 '17 at 22:13
  • This last solution I presented is also not so good, the user might start a Project, insert a Position and don't insert Elements because he's done for the day, he wants to save to continue in the next day, and at that point, there is no association between Project_Rev and Position! – f4d0 Dec 10 '17 at 22:15

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.


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:

enter image description here

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.