Re: Design a new database - Mailing list pgsql-general

Geovan Rodriguez Collazo wrote:
  >I need help creating a new database.
  >
  >It should be something like this:
  >
  >Table1                                  Table2
  >------                                  ------
  >t1_ID (int)                             t2_ID (int)
  >data1 (text)                            data1 (text)
  >data2 (text)                            data2 (text)
  >...                                     ...
  >lnks (??) <--  This should point        lnks (??) <--  This should point
  >               to one or more                          to one or more
  >               rows of the second                      rows of the first
  >               table.                                  table.


This isn't the way to do it in a RDBMS; you should forget about having a
cross-reference field in each table, which is a style of things
appropriate to PICK, and instead define a third cross-referencing table:

CREATE TABLE t1_t2_links (
    t1_id int REFERENCES table1 (t1_id)
                     ON UPDATE CASCADE ON DELETE RESTRICT,
        t2_id int REFERENCES table2 (t2_id)
                     ON UPDATE CASCADE ON DELETE RESTRICT,
    PRIMARY KEY (t1_id, t2_id)
)

  >An example:
  >
  >Table1                                           Table2
  >
  >t1_id | data1 | data2 | ... | lnks      |        t2_id | data1 | data2 |
  >... | lnks      |
  >------|-------|-------|-----|-----------|
  >------|-------|-------|-----|-----------|
  >A01   |TOMATOE|XXX    |...  |116,118    |        115   |XXX    |XXX
  >|...  |A01,A03    |
  >A02   |RICE   |XXX    |...  |118        |        116   |XXX    |XXX
  >|...  |A02,A03,B01|
  >A03   |BEENS  |XXX    |...  |115,117,118|        117   |XXX    |XXX
  >|...  |           |
  >B01   |MILK   |XXX    |...  |117,118    |        118   |XXX    |XXX
  >|...  |A01,B01    |
  >
  >
  >I'm thinking on the type of the field "lnks", if I use an array of
  >char(20), how can I do the following:
  >
  >
  >I need to make a query like:
  >
  >  SELECT t1_id FROM Table1 WHERE lnks='117';
  >and to have an answer like this:
  >
  >t1_id
  >-----
  >A03
  >B01
  >

SELECT t1_id FROM table1 AS t, t1_t2_links AS x
  WHERE t.t1_id = x.t1_id AND x.t2_id = 117;

  >
  >And to make a query like:
  >
  >  SELECT Table1.* FROM Table1,Table2 WHERE Table2.t2_id='118';
  >and to have an answer like this:
  >
  >  t1_id | data1 | data2 | ... | lnks      |
  >  ------|-------|-------|-----|-----------|
  >  A01   |TOMATOE|XXX    |...  |116,118    |
  >  B01   |MILK   |XXX    |...  |117,118    |
  >

I don't think it is possible to get the other links in table 2 into
one row with a cross-referencing table, but you can get them into
multiple rows:

SELECT t.*, x.t2_id FROM table1 AS t, t1_t2_links AS x
  WHERE t.t1_id = x.t1_id AND t.t1_id IN
    (SELECT t1_id FROM t1_t2_links WHERE x.t2_id = 118)
  ORDER BY t.t1_id, x.t2_id;

  >
  >Which other type could I use for "lnks", maybe just text ("115#117#118")
  >and make this query:
  >
  >  SELECT t1_id FROM Table1 WHERE lnks like '%117%';

Updating this field would be very cumbersome; much easier to add a new
cross-referencing record to the t1_t2_links table.

On my first essays in PostgreSQL (having come from PICK) I tried to do
things in the way that you are thinking of, but found it far too
difficult to maintain the data.

Another problem with your approach is that you cannot have the database
maintain referential integrity with your cross-referencing.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Delight thyself also in the LORD; and he shall give
      thee the desires of thine heart."          Psalms 37:4