MIME-Version: 1.0
In-Reply-To: <NAE0jgk--3-2@tutanota.com>
References: <NA0w1JZ--J-2@tutanota.com>
 <CABUk_4iSF9MagtgSX9ox5ZOqX0hMLo+FVN=xcJHksSW=aUriHQ@mail.gmail.com>
 <CAKFQuwb+eL7bYaE_9b9t7mGGj=c5SCYPmP0UoWitc86=+1UnCQ@mail.gmail.com>
 <NAE0jgk--3-2@tutanota.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Wed, 24 Aug 2022 06:47:44 -0700
Message-ID: 
 <CAKFQuwYnANC8y5uSJtA8DvjvXUOwhuZJkjyCy8xFB1LFVqrE5w@mail.gmail.com>
Subject: Re: How to use a cross column exclude constraint
To: "awolchute@tutanota.com" <awolchute@tutanota.com>
Cc: Pgsql Novice <pgsql-novice@lists.postgresql.org>,
	Mchidamparam <mchidamparam@gmail.com>
Content-Type: multipart/alternative; boundary="000000000000f8073f05e6fceca1"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKFQuwYnANC8y5uSJtA8DvjvXUOwhuZJkjyCy8xFB1LFVqrE5w%40mail.gmail.com>
Precedence: bulk

--000000000000f8073f05e6fceca1
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wednesday, August 24, 2022, <awolchute@tutanota.com> wrote:

> Hi,
>
> Thank you for the insight!
>
> How would you go about modeling my problem correctly?
>
> The domain constraints are:
> - there are many "records"
> - there are 1:1 links between "records", and the links (table) contain a
> lot of information about the link (so adding a record_id (fk) to the
> records table would also add a ton of columns).
> - the links are bidirectional
> - each "record" can be linked with exactly one "record", so a record
> linking to another does not allow the record being referenced to be in an=
y
> other link either.
> - a graph of records and their connections (links) must be efficiently
> queried / formed
>

Node: [node_id PK, edge_id {FK edge.edge_id}, {Unique: node_id, edge_id)]
Edge: [edge_id PK, =E2=80=A6]
Node-Edge: [(node_id, edge_id) {FK node.node_id, node.edge_id}, slot {check
slot in (1,2); not null}, {PK: (edge_id, slot)}, {Unique: node_id}]

That doesn=E2=80=99t enforce =E2=80=9Cnot zero=E2=80=9D or missing records,=
 which is possible but
generally a pain, but does enforce that a node may have at most one edge,
and each edge has at most two nodes.

With a deferred not null constraint on node.esge_id I think you can solve
prevent missing links problem, assuming you always add nodes in pairs.
You=E2=80=99d do so ething similar with edge.edge_id if you wanted to avoid
dangling edges (edges without nodes).

David J.

--000000000000f8073f05e6fceca1
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wednesday, August 24, 2022,  &lt;<a href=3D"mailto:awolchute@tutanota.co=
m">awolchute@tutanota.com</a>&gt; wrote:<br><blockquote class=3D"gmail_quot=
e" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
 =20
   =20
 =20
  <div>
<div>Hi,<br></div><div dir=3D"auto"><br></div><div dir=3D"auto">Thank you f=
or the insight!<br></div><div dir=3D"auto"><br></div><div dir=3D"auto">How =
would you go about modeling my problem correctly?<br></div><div dir=3D"auto=
"><br></div><div dir=3D"auto">The domain constraints are:<br></div><div dir=
=3D"auto">- there are many &quot;records&quot;<br></div><div dir=3D"auto">-=
 there are 1:1 links between &quot;records&quot;, and the links (table) con=
tain a lot of information about the link (so adding a record_id (fk) to the=
 records table would also add a ton of columns).<br></div><div dir=3D"auto"=
>- the links are bidirectional<br></div><div dir=3D"auto">- each &quot;reco=
rd&quot; can be linked with exactly one &quot;record&quot;, so a record lin=
king to another does not allow the record being referenced to be in any oth=
er link either.<br></div><div dir=3D"auto">- a graph of records and their c=
onnections (links) must be efficiently queried / formed</div></div></blockq=
uote><div><br></div><div>Node: [node_id PK, edge_id {FK edge.edge_id}, {Uni=
que: node_id, edge_id)]</div><div>Edge: [edge_id PK, =E2=80=A6]</div><div>N=
ode-Edge: [(node_id, edge_id) {FK node.node_id, node.edge_id}, slot {check =
slot in (1,2); not null}, {PK: (edge_id, slot)}, {Unique: node_id}]</div><d=
iv><br></div><div>That doesn=E2=80=99t enforce =E2=80=9Cnot zero=E2=80=9D o=
r missing records, which is possible but generally a pain, but does enforce=
 that a node may have at most one edge, and each edge has at most two nodes=
.</div><div><br></div><div>With a deferred not null constraint on node.esge=
_id I think you can solve prevent missing links problem, assuming you alway=
s add nodes in pairs.=C2=A0 You=E2=80=99d do so ething similar with edge.ed=
ge_id if you wanted to avoid dangling edges (edges without nodes).</div><di=
v><br></div><div>David J.</div><div><br></div>

--000000000000f8073f05e6fceca1--