'Persist state on derived data structure
I have a graph in my app, that I need to save to a db. So, first thing, I created a Nodes table and Edges table:
Node
- id PK
- name TEXT
- x INT
- y INT
Edge
- id PK
- from FK REFERENCES Node
- to FK REFERENCES Node
But, we will have multiple events happening between two nodes, which need their own table. We will have multiple events between one node pair. It makes sense for our db to remove the Edge table and instead use an events table.
Node
- id PK
- name TEXT
- x INT
- y INT
Event
- id PK
- from FK REFERENCES Node
- to FK REFERENCES Node
- event TEXT
- date DATE
I will then derive the edges simply from the events. If one or more events occur between A and B, an edge will be rendered between A and B.
So far, so good.
But now, we want to persist more detailed layout information. The edge between two nodes is computed and rendered, but we want it to have a user-set offset that is persisted in the DB (much like Miro or Figjam, or Keynote / Visio etc).
That is obviously not something that we want to add to each Event separately, but save only once for each Edge.
I can do that by putting back the Edge table:
Node
- id PK
- name TEXT
- x INT
- y INT
Event
- id PK
- from FK REFERENCES Node
- to FK REFERENCES Node
- event TEXT
- date DATE
Edge
- id PK
- from FK REFERENCES Node
- to FK REFERENCES Node
- offset INT
But now I need to create an Edge row if a new Event is made with a unique from-to combination (but not if this from-to combi already existed), need to delete the Edge if the last Event with its particular from-to combo is deleted to prevent orphan Edges to exist, and so on and so forth. There is simply some redundancy in my schema, and I'm wondering if there is a better way to set this up to get rid of this.
Is there a better way? If so, what solutions would you suggest?
Note: I understand that the story I depict would suggest I remove the from and to from the Event table and back to the Edges table, but my actual story is more complex and there is good reason for me to keep from and to in the Event table. Please consider it a non-option to move the from-to keys away from the Events table.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
