public inbox for [email protected]
help / color / mirror / Atom feedFrom: David G. Johnston <[email protected]>
To: [email protected] <[email protected]>
Cc: Pgsql Novice <[email protected]>
Cc: Mchidamparam <[email protected]>
Subject: Re: How to use a cross column exclude constraint
Date: Wed, 24 Aug 2022 06:47:44 -0700
Message-ID: <CAKFQuwYnANC8y5uSJtA8DvjvXUOwhuZJkjyCy8xFB1LFVqrE5w@mail.gmail.com> (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]>
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], [email protected]
Subject: Re: How to use a cross column exclude constraint
In-Reply-To: <CAKFQuwYnANC8y5uSJtA8DvjvXUOwhuZJkjyCy8xFB1LFVqrE5w@mail.gmail.com>
* 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