Update the metadata visualization to account for primary to primary key relationships #2806
Description
Problem Description
In an upcoming feature (#2779), we will allow schemas where a primary key of a table can also be a foreign key in another table. As a result, users can specify a 1-to-1 OR 1-to-(0 or 1) relationship between tables.
We would like to update the metadata visualization to account for this and show the PK to PK relationship between the two tables.
Currently, the metadata visualization shows a one to many cardinality between two tables where the primary key is the same:
Expected behavior
The metadata visualization should show the proper edge to indicate the 1 : 0..1 (one to zero/one relationship) between two tables:
- The straight perpendicular line shows a one relationship.
- The circle & straight perpendicular line indicates that it could be zero or one relationship. The data could show either an exact 1-to-1 relationship or a 1-to-(0 or 1) relationship. In the metadata, we do not have access to the data. Therefore, we will show a 1-to-(0 or 1) relationship because it will indicate which side is parent and which side is the child.
Additional context
- Per the Crow's Foot Notation, a one to many relationship should be depicted in the following manner:
0..1 - zero or one - is depicted as a circle and a perpendicular line to the connector line. The circle is always nearer to the center of the relationship.
- Another option, which was considered, was to show a 1:1 relationship with two straight perpendicular lines:
