Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oRBKo-0002Ot-IL for pgsql-novice@arkaria.postgresql.org; Thu, 25 Aug 2022 11:47:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oRBKm-0007aP-MK for pgsql-novice@arkaria.postgresql.org; Thu, 25 Aug 2022 11:47:40 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oRBKm-0007Zt-AV for pgsql-novice@lists.postgresql.org; Thu, 25 Aug 2022 11:47:40 +0000 Received: from w1.tutanota.de ([81.3.6.162]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oRBKi-0006vf-T7 for pgsql-novice@lists.postgresql.org; Thu, 25 Aug 2022 11:47:37 +0000 Received: from w3.tutanota.de (unknown [192.168.1.164]) by w1.tutanota.de (Postfix) with ESMTP id 8E95CFBF8D3; Thu, 25 Aug 2022 11:47:35 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; t=1661428055; s=s1; d=tutanota.com; h=From:From:To:To:Subject:Subject:Content-Description:Content-ID:Content-Type:Content-Type:Content-Transfer-Encoding:Cc:Cc:Date:Date:In-Reply-To:In-Reply-To:MIME-Version:MIME-Version:Message-ID:Message-ID:Reply-To:References:References:Sender; bh=KkTnowxknGzNoYc4LQvdXk9BK9air8L6YYm9+409VmU=; b=MlFj9SBBiIvpPD19WwxaYwakx5sACBypP9/beoN3mztW0diIG0Z1ZeUMZyAcGYUc r64y6plAyVhHb6fXWaMKide0uKLYyK5KekmnbuAQ9/tJKEqXHPf/Cdy6jdHUuuqc74k eNldJchhUSd35Spd1VA33+EFdhi7prKvYVOsepDLSVHZvw5DradVeAfVieitgR03f3c l2+AaVaAWMgCz14mK1FYEqUPLvb3JdDtrmLrl1WyLbpVRol5bp1ezbpDDUeKAOig3B2 27UaLVR3a+WDnqnsGpm1IyENGt2OrYLn9DnhrtqLi0taKGWFnqJ46EirOfdWig31yY5 lyTN3uSDXQ== Date: Thu, 25 Aug 2022 13:47:35 +0200 (CEST) From: awolchute@tutanota.com To: "David G. Johnston" Cc: Pgsql Novice Message-ID: In-Reply-To: References: Subject: Re: How to use a cross column exclude constraint MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_4910_997264664.1661428055569" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_4910_997264664.1661428055569 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi, What does this part of your annotation mean: "slot {check slot in (1,2); no= t null}, {PK: (edge_id, slot)}, {Unique: node_id}]"? The whole Node-Edge pa= rt is a bit fuzzy for me. I have written the following schema: -- node CREATE TABLE api_endpoints ( =C2=A0 id UUID PRIMARY KEY, =C2=A0 api_endoint_edge_id UUID REFERENCES api_endpoint_edges (id), =C2=A0 UNIQUE (id, api_endpoint_edge_id), -- did I interpret this correctly= ? =C2=A0 ...=C2=A0 =C2=A0 ); -- edge CREATE TABLE api_endpoint_edges ( =C2=A0 id UUID PRIMARY KEY, =C2=A0 ...=C2=A0 -- ton of data=C2=A0 ); -- node-edge, how should i name this table? is just dropping the pluralizat= ion readable? CREATE TABLE api_endpoint_edge ( =C2=A0 id UUID PRIMARY KEY, =C2=A0 api_endoint_id UUID REFERENCES=C2=A0api_endpoints (id), =C2=A0 api_endoint_edge_id UUID PRIMARY KEY REFERENCES=C2=A0api_endpoints (= id), =C2=A0 -- what is slot? ); Thank you for helping me get started with Postgres! > > Aug 24, 2022, 13:47 by david.g.johnston@gmail.com: > >> 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 reco= rds table would also add a ton of columns). >>> - the links are bidirectional >>> - each "record" can be linked with exactly one "record", so a record li= nking to another does not allow the record being referenced to be in any ot= her 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 {ch= eck 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 recor= ds, which is possible but generally a pain, but does enforce that a node ma= y 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 solv= e prevent missing links problem, assuming you always add nodes in pairs.=C2= =A0 You=E2=80=99d do so ething similar with edge.edge_id if you wanted to a= void dangling edges (edges without nodes). >> >> David J. >> >> > > ------=_Part_4910_997264664.1661428055569 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi,

What does this part of your annotation mean: "slot {check slot in (1,2); = not null}, {PK: (edge_id, slot)}, {Unique: node_id}]"? The whole Node-Edge = part is a bit fuzzy for me.

I have written the following schema:
<= br>
-- node
CREATE TABLE = api_endpoints (
  id UUID PRIMARY KEY,
<= /div>
  api_endoint_edge_id UUID REFERENCES api_endpo= int_edges (id),
  UNIQUE (id, api_endpoint_= edge_id), -- did I interpret this correctly?
&nb= sp; ...   
);

-- edge
CREATE TA= BLE api_endpoint_edges (
  id UUID PRIMARY = KEY,
  ...  -- ton of data 
);

-- node-edge, how should i name this table? is just dropping the plura= lization readable?
CREATE TABLE api_endpoint_edg= e (
  id UUID PRIMARY KEY,
  api_endoint_id UUID REFERENCES api_endpoints (id),
  api_endoint_edge_id UUID PRIMARY KEY REFER= ENCES api_endpoints (id),
  -- what is= slot?
);

<= div dir=3D"auto">Thank you for helping me get started with Postgres!

=

Aug 24, 2022, 13:47 by david.g.johnston@gm= ail.com:
On Wednesd= ay, August 24, 2022, <awolchute@tutanota.com> wrote= :
= Hi,

Thank you for th= e 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 bidirect= ional
- each "record" can be linked with exactly= one "record", so a record linking to another does not allow the record bei= ng referenced to be in any other link either.
- = a graph of records and their connections (links) must be efficiently querie= d / formed

Node: [node_id P= K, edge_id {FK edge.edge_id}, {Unique: node_id, edge_id)]
Edg= e: [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: (e= dge_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 mos= t one edge, and each edge has at most two nodes.

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

Dav= id J.



------=_Part_4910_997264664.1661428055569--