Обсуждение: Recursive relationship - preventing cross-index entries.

Поиск
Список
Период
Сортировка

Recursive relationship - preventing cross-index entries.

От
"Andrew Maclean"
Дата:
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/
___________________________________________

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

От
Michael Glaesemann
Дата:
[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

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

От
Richard Huxton
Дата:
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

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

От
Sean Davis
Дата:
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

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

От
"Andrew Maclean"
Дата:
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/
___________________________________________