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_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
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:
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!
I thought in creating a middle ground table for
Element, where for each
Position combination there is a
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?