public inbox for [email protected]  

help / color / mirror / Atom feed

From: [email protected]
To: David G. Johnston <[email protected]>
Cc: Pgsql Novice <[email protected]>
Subject: Re: How to use a cross column exclude constraint
Date: Thu, 25 Aug 2022 13:47:35 +0200 (CEST)
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<CABUk_4iSF9MagtgSX9ox5ZOqX0hMLo+FVN=xcJHksSW=aUriHQ@mail.gmail.com>
	<CAKFQuwb+eL7bYaE_9b9t7mGGj=c5SCYPmP0UoWitc86=+1UnCQ@mail.gmail.com>
	<[email protected]>
	<CAKFQuwYnANC8y5uSJtA8DvjvXUOwhuZJkjyCy8xFB1LFVqrE5w@mail.gmail.com-NAF6-SO----2>
	<[email protected]>

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:

-- node
CREATE TABLE api_endpoints (
  id UUID PRIMARY KEY,
  api_endoint_edge_id UUID REFERENCES api_endpoint_edges (id),
  UNIQUE (id, api_endpoint_edge_id), -- did I interpret this correctly?
  ...   
);

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

-- node-edge, how should i name this table? is just dropping the pluralization readable?
CREATE TABLE api_endpoint_edge (
  id UUID PRIMARY KEY,
  api_endoint_id UUID REFERENCES api_endpoints (id),
  api_endoint_edge_id UUID PRIMARY KEY REFERENCES api_endpoints (id),
  -- what is slot?
);

Thank you for helping me get started with Postgres!


>
> Aug 24, 2022, 13:47 by [email protected]:
>
>> On Wednesday, August 24, 2022,  <>> [email protected]>> > 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 any 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, …]
>> 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’t enforce “not zero” 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’d do so ething similar with edge.edge_id if you wanted to avoid dangling edges (edges without nodes).
>>
>> David J.
>>
>>
>
>

view thread (7+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: How to use a cross column exclude constraint
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox