Recursive relationship - preventing cross-index entries.

Started by Andrew Macleanalmost 19 years ago5 messagesgeneral

andrew.amaclean@gmail.com

almost 19 years ago

I got no answer so I am trying again.

In a nutshell, if I have a recrusive relationship as outlined below, how do
I implement a rule for the adjustments table that prevents the entry of an
Id into the Ref column if the id exists in the Id column and vice versa?

If I have a payments table which holds an Id and a payment and I also have
an adjustments table that holds a payment id and a reference id so that
adjustments can be made to payments.
So the payments table looks like this:
Id Payment
1 500.0
2 100.0
3 1000.0
4 50.0
5 750.0
6 50.0
7 800.0
8 1200.0

and the adjustments table looks like this:
Id Ref
1 2
3 4
1 6
3 5
The idea is that, if for example Id=1 is a credit dard payment, then entries
2 and 6 could be payments that are already included in the credit card
payment so we need to adjust the total payment to take this into account.

This means that the payment for Id=1 ($500) in the payments table needs to
be reduced by $100 (ref=2) and $50 (ref=6) , similarly, the payment for Id=3
($1000) needs to be reduced by $850). So the question is:

How do I design the adjustments table to ensure that:
a) For any value entered in the Id column a check should occur to ensure
that it does not exist in the Ref column.
b) For any value entered in the Ref column, a check should occur to
ensure that it does not exist in the Id column.

In other words, looking at the adjustments table, I should be prevented
from entering 2,4,6,5 in the Id column and 1, 3 in the Ref column.

I can easily prevent entries like (5,3) Ok see : id_ref_pair_idx below or
Id==Ref entries like 2,2.

Here is a dump of the database if you want to experiment:
----------------------------------------------------------------------------------

--
-- PostgreSQL database dump
--

-- Started on 2007-06-08 13:42:30

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- TOC entry 1619 (class 1262 OID 16821)
-- Dependencies: 1618
-- Name: Test; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON DATABASE "Test" IS 'Test database.';

--
-- TOC entry 1620 (class 0 OID 0)
-- Dependencies: 4
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'Standard public schema';

--
-- TOC entry 265 (class 2612 OID 16389)
-- Name: plperl; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE PROCEDURAL LANGUAGE plperl;

--
-- TOC entry 264 (class 2612 OID 16386)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE PROCEDURAL LANGUAGE plpgsql;

--
-- TOC entry 266 (class 2612 OID 16391)
-- Name: pltcl; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE PROCEDURAL LANGUAGE pltcl;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 1273 (class 1259 OID 16862)
-- Dependencies: 1606 4
-- Name: Adjustments; Type: TABLE; Schema: public; Owner: postgres;
Tablespace:
--

CREATE TABLE "Adjustments" (
id integer NOT NULL,
ref integer NOT NULL,
CONSTRAINT "Check01" CHECK ((id <> ref))
);

ALTER TABLE public."Adjustments" OWNER TO postgres;

--
-- TOC entry 1622 (class 0 OID 0)
-- Dependencies: 1273
-- Name: TABLE "Adjustments"; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE "Adjustments" IS 'Used to correct for double counting on
Payments';

--
-- TOC entry 1623 (class 0 OID 0)
-- Dependencies: 1273
-- Name: COLUMN "Adjustments".id; Type: COMMENT; Schema: public; Owner:
postgres
--

COMMENT ON COLUMN "Adjustments".id IS 'The Id that we have to correct the
payment on.';

--
-- TOC entry 1624 (class 0 OID 0)
-- Dependencies: 1273
-- Name: COLUMN "Adjustments".ref; Type: COMMENT; Schema: public; Owner:
postgres
--

COMMENT ON COLUMN "Adjustments".ref IS 'The id that is used to correct the
payment.';

--
-- TOC entry 1625 (class 0 OID 0)
-- Dependencies: 1273
-- Name: CONSTRAINT "Check01" ON "Adjustments"; Type: COMMENT; Schema:
public; Owner: postgres
--

COMMENT ON CONSTRAINT "Check01" ON "Adjustments" IS 'An Id cannot be the
same as a ref.';

--
-- TOC entry 1272 (class 1259 OID 16824)
-- Dependencies: 1605 4
-- Name: Payments; Type: TABLE; Schema: public; Owner: postgres; Tablespace:

CREATE TABLE "Payments" (
"Id" integer NOT NULL,
"Payment" numeric(18,1) DEFAULT 0.0
);

ALTER TABLE public."Payments" OWNER TO postgres;

--
-- TOC entry 1271 (class 1259 OID 16822)
-- Dependencies: 1272 4
-- Name: Payments_Id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE "Payments_Id_seq"
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

ALTER TABLE public."Payments_Id_seq" OWNER TO postgres;

--
-- TOC entry 1626 (class 0 OID 0)
-- Dependencies: 1271
-- Name: Payments_Id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner:
postgres
--

ALTER SEQUENCE "Payments_Id_seq" OWNED BY "Payments"."Id";

--
-- TOC entry 1627 (class 0 OID 0)
-- Dependencies: 1271
-- Name: Payments_Id_seq; Type: SEQUENCE SET; Schema: public; Owner:
postgres
--

SELECT pg_catalog.setval('"Payments_Id_seq"', 1, false);

--
-- TOC entry 1604 (class 2604 OID 16826)
-- Dependencies: 1272 1271 1272
-- Name: Id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE "Payments" ALTER COLUMN "Id" SET DEFAULT
nextval('"Payments_Id_seq"'::regclass);

--
-- TOC entry 1615 (class 0 OID 16862)
-- Dependencies: 1273
-- Data for Name: Adjustments; Type: TABLE DATA; Schema: public; Owner:
postgres
--

COPY "Adjustments" (id, ref) FROM stdin;
1 2
3 4
1 6
3 5
\.

--
-- TOC entry 1614 (class 0 OID 16824)
-- Dependencies: 1272
-- Data for Name: Payments; Type: TABLE DATA; Schema: public; Owner:
postgres
--

COPY "Payments" ("Id", "Payment") FROM stdin;
1 500.0
2 100.0
3 1000.0
4 50.0
5 750.0
6 50.0
7 800.0
8 1200.0
\.

--
-- TOC entry 1608 (class 2606 OID 16829)
-- Dependencies: 1272 1272
-- Name: Payments_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres;
Tablespace:
--

ALTER TABLE ONLY "Payments"
ADD CONSTRAINT "Payments_pkey" PRIMARY KEY ("Id");

--
-- TOC entry 1611 (class 2606 OID 16866)
-- Dependencies: 1273 1273 1273
-- Name: id_ref_pk; Type: CONSTRAINT; Schema: public; Owner: postgres;
Tablespace:
--

ALTER TABLE ONLY "Adjustments"
ADD CONSTRAINT id_ref_pk PRIMARY KEY (id, ref);

--
-- TOC entry 1609 (class 1259 OID 16878)
-- Dependencies: 1273 1273
-- Name: id_ref_pair_idx; Type: INDEX; Schema: public; Owner: postgres;
Tablespace:
--

CREATE UNIQUE INDEX id_ref_pair_idx ON "Adjustments" USING btree ((CASE WHEN
(id > ref) THEN ARRAY[id, ref] ELSE ARRAY[ref, id] END)) WITH
(fillfactor=100);

--
-- TOC entry 1612 (class 2606 OID 16867)
-- Dependencies: 1272 1607 1273
-- Name: id_Payments_id_fk; Type: FK CONSTRAINT; Schema: public; Owner:
postgres
--

ALTER TABLE ONLY "Adjustments"
ADD CONSTRAINT "id_Payments_id_fk" FOREIGN KEY (id) REFERENCES
"Payments"("Id");

--
-- TOC entry 1613 (class 2606 OID 16872)
-- Dependencies: 1607 1273 1272
-- Name: ref_Payments_id_fk; Type: FK CONSTRAINT; Schema: public; Owner:
postgres
--

ALTER TABLE ONLY "Adjustments"
ADD CONSTRAINT "ref_Payments_id_fk" FOREIGN KEY (ref) REFERENCES
"Payments"("Id");

--
-- TOC entry 1621 (class 0 OID 0)
-- Dependencies: 4
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;

-- Completed on 2007-06-08 13:42:30

--
-- PostgreSQL database dump complete
--

___________________________________________
Andrew J. P. Maclean
Centre for Autonomous Systems
The Rose Street Building J04
The University of Sydney 2006 NSW
AUSTRALIA
Ph: +61 2 9351 3283
Fax: +61 2 9351 7474
URL: http://www.cas.edu.au/
___________________________________________

Michael Glaesemann

grzm@seespotcode.net

almost 19 years ago

In reply to: Andrew Maclean (#1)

Re: [NOVICE] Recursive relationship - preventing cross-index entries.

[Removing pgsql-novice. Please don't cross-post. Choose one list or
another at a time. ]

On Jun 19, 2007, at 23:04 , Andrew Maclean wrote:

I got no answer so I am trying again.

In a nutshell, if I have a recrusive relationship as outlined
below, how do I implement a rule for the adjustments table that
prevents the entry of an Id into the Ref column if the id exists in
the Id column and vice versa?

If I have a payments table which holds an Id and a payment and I
also have an adjustments table that holds a payment id and a
reference id so that adjustments can be made to payments.
So the payments table looks like this:
Id Payment
1 500.0
2 100.0
3 1000.0
4 50.0
5 750.0
6 50.0
7 800.0
8 1200.0

and the adjustments table looks like this:
Id Ref
1 2
3 4
1 6
3 5
The idea is that, if for example Id=1 is a credit dard payment,
then entries 2 and 6 could be payments that are already included in
the credit card payment so we need to adjust the total payment to
take this into account.

I guess I don't really understand why your schema is set up this way.
It seems like the amounts for 2, 4, 5, and 6 are of a different type
than those of 1 and 3, so I'd put them in two different tables. It
seems that 2, 4, 5, and 6 are more like amounts due, while 1 and 3
are payments made against those due amounts. This

CREATE TABLE accounts_receivable
(
accounts_receivable_id INTEGER PRIMARY KEY
, amount NUMERIC NOT NULL
);

CREATE TABLE receipts
(
receipt_id INTEGER PRIMARY KEY
, amount NUMERIC NOT NULL
);

CREATE TABLE accounts_receivable_receipts
(
accounts_receivable_id INTEGER NOT NULL
REFERENCES accounts_receivable
, receipt_id INTEGER NOT NULL
REFERENCES receipts
, PRIMARY KEY (accounts_receivable_id, receipt_id)
);

So, using the numbers you have above, you'd have

INSERT INTO accounts_receivable (accounts_receivable_id, amount)
VALUES (2, 100.0), (4, 50.0), (5, 750.0), (6, 50.0);

INSERT INTO receipts (receipt_id, amount)
VALUES (1, 500.0), (3, 1000.0);

INSERT INTO accounts_receivable_receipts (accounts_receivable_id,
receipt_id)
VALUES (2, 1), (4, 3), (6, 1), (5, 3);

I have not done much accounting-style design, and I don't think this
is really the best way to set these up (for example, I think it's a
bit odd to map these amounts against each other without indicating
how much of the amount is matched), but without more information
about your business logic, I don't really know what else to suggest.

Hope this helps.

Michael Glaesemann
grzm seespotcode net

Richard Huxton

dev@archonet.com

almost 19 years ago

In reply to: Michael Glaesemann (#2)

Re: [NOVICE] Recursive relationship - preventing cross-index entries.

Michael Glaesemann wrote:

INSERT INTO accounts_receivable_receipts (accounts_receivable_id,
receipt_id)
VALUES (2, 1), (4, 3), (6, 1), (5, 3);

I have not done much accounting-style design, and I don't think this is
really the best way to set these up (for example, I think it's a bit odd
to map these amounts against each other without indicating how much of
the amount is matched), but without more information about your business
logic, I don't really know what else to suggest.

In most I've seen, you have an "allocations" table linking payments
received against individual invoices. So, you might have payment 1234
with ï¿½100 against inv #10001 and ï¿½150 against inv #10002.

The allocations table tends to have an "amount unallocated" column too,
imposing an order to the allocations. Not strictly necessary from a
database point of view, but I suspect left over from manual methods of
working.

--
Richard Huxton
Archonet Ltd

Sean Davis

sdavis2@mail.nih.gov

almost 19 years ago

In reply to: Andrew Maclean (#1)

Re: [NOVICE] Recursive relationship - preventing cross-index entries.

Andrew Maclean wrote:

I got no answer so I am trying again.

In a nutshell, if I have a recrusive relationship as outlined below, how
do I implement a rule for the adjustments table that prevents the entry
of an Id into the Ref column if the id exists in the Id column and vice
versa?

If I have a payments table which holds an Id and a payment and I also
have an adjustments table that holds a payment id and a reference id so
that adjustments can be made to payments.
So the payments table looks like this:
Id Payment
1 500.0
2 100.0
3 1000.0
4 50.0
5 750.0
6 50.0
7 800.0
8 1200.0

and the adjustments table looks like this:
Id Ref
1 2
3 4
1 6
3 5
The idea is that, if for example Id=1 is a credit dard payment, then
entries 2 and 6 could be payments that are already included in the
credit card payment so we need to adjust the total payment to take this
into account.

This means that the payment for Id=1 ($500) in the payments table needs
to be reduced by $100 (ref=2) and $50 (ref=6) , similarly, the payment
for Id=3 ($1000) needs to be reduced by $850). So the question is:

How do I design the adjustments table to ensure that:
a) For any value entered in the Id column a check should occur to
ensure that it does not exist in the Ref column.
b) For any value entered in the Ref column, a check should occur to
ensure that it does not exist in the Id column.

In other words, looking at the adjustments table, I should be
prevented from entering 2,4,6,5 in the Id column and 1, 3 in the Ref
column.

I th8ink you can put a trigger on the table that can check the
constraints.

http://www.postgresql.org/docs/8.2/static/triggers.html

However, I wonder whether it might not make more sense to go with an
account system, with an account balance and credits and debits to the
account. Is the system you are proposing really the best data model?

Sean

Andrew Maclean

andrew.amaclean@gmail.com

almost 19 years ago

In reply to: Sean Davis (#4)

Re: [NOVICE] Recursive relationship - preventing cross-index entries.

Thanks to you all for all your help and comments.
I finally ended up creating a trigger to check the constraints. This has the
added benefit that more than one constraint can be checked in the one
trigger.
As to whether it is the best model or not for what I want to do. This
question is more difficult to answer, but after giving it a lot of thought,
I think it is, mainly on the grounds of elegance and convenience. However
when I construct a GUI all may change!

Thankyou all for your input.

On 6/20/07, Sean Davis <sdavis2@mail.nih.gov> wrote:

Andrew Maclean wrote:

I got no answer so I am trying again.

In a nutshell, if I have a recrusive relationship as outlined below, how
do I implement a rule for the adjustments table that prevents the entry
of an Id into the Ref column if the id exists in the Id column and vice
versa?

If I have a payments table which holds an Id and a payment and I also
have an adjustments table that holds a payment id and a reference id so
that adjustments can be made to payments.
So the payments table looks like this:
Id Payment
1 500.0
2 100.0
3 1000.0
4 50.0
5 750.0
6 50.0
7 800.0
8 1200.0

and the adjustments table looks like this:
Id Ref
1 2
3 4
1 6
3 5
The idea is that, if for example Id=1 is a credit dard payment, then
entries 2 and 6 could be payments that are already included in the
credit card payment so we need to adjust the total payment to take this
into account.

This means that the payment for Id=1 ($500) in the payments table needs
to be reduced by $100 (ref=2) and $50 (ref=6) , similarly, the payment
for Id=3 ($1000) needs to be reduced by $850). So the question is:

How do I design the adjustments table to ensure that:
a) For any value entered in the Id column a check should occur to
ensure that it does not exist in the Ref column.
b) For any value entered in the Ref column, a check should occur to
ensure that it does not exist in the Id column.

In other words, looking at the adjustments table, I should be
prevented from entering 2,4,6,5 in the Id column and 1, 3 in the Ref
column.

I th8ink you can put a trigger on the table that can check the
constraints.

http://www.postgresql.org/docs/8.2/static/triggers.html

However, I wonder whether it might not make more sense to go with an
account system, with an account balance and credits and debits to the
account. Is the system you are proposing really the best data model?

Sean

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--
___________________________________________
Andrew J. P. Maclean
Centre for Autonomous Systems
The Rose Street Building J04
The University of Sydney 2006 NSW
AUSTRALIA
Ph: +61 2 9351 3283
Fax: +61 2 9351 7474
URL: http://www.acfr.usyd.edu.au/
___________________________________________