Re: Retrieving many-to-many relationships (maybe off topic/wrong group?)

From: Philip Lijnzaad <lijnzaad_at_ebi.ac.uk>
Date: 13 Sep 2001 17:28:25 +0100
Message-ID: <u766ancaxi.fsf_at_sol6.ebi.ac.uk>


> X-No-archive: yes
> Hello folks,

> I've got a little question concerning the processing of a many-to-many
> relationship. I will use a concrete example for my problem, although I came
> across this issue quite often; now I wonder how professionals like YOU would
> do it :-)

> Basically, I've got a 'person' relation, a 'country' relation and a 'visits'
> relation providing a many-to-many relation between person and country.

> I'd like to print a list of all (or some, no matter) persons, and for each
> of them a comma-separated enumeration of all countries they visit. Clearly
> this is an application layer issue, however, I hope you don't mind ;-).

Something like

  SELECT p.person_id, p.surname, c.name
  FROM Persons p, Persons_Countries pc, Countries c   WHERE p.person_id =pc.person_id
    AND pc.country_id = c.id
  /* AND p.surname_ucase like '%SMITH%' etc. */   ORDER BY p.person_id, c.country_id;

> When retrieving the data from a RDBMS, would you JOIN the relations, thus
> producing several tuples for each person with one different country each?

yes; I would filter the stuff (i.e. get rid of the recurring person_id's using the person_id). Let the DBMS do as much as possibly for you; depending on your set-up and application, it may even be cheaper to get all the tuples, rather than the 20% you're interested in (the like '%SMITH%' bit), and throw away the 80% locally too.

> By sorting appropriately (e. g.on person's PK) and adding some lines of
> code, one could achieve the goal. However, there's some overhead (memory,

are usually immeasurably cheap, as are CPU cycles

> network)

why network?

> for the data that is processed several times.

> On the other hand, one could first select all persons and then query for
> each one's countries successively. The disadvantage is a higher amount of
> statements to process.

no, the amount of network round trips; they'll be hurting.

> (If this is the wrong newsgroup, could you please point me to another one?)

seems OK to me. Cheers,

                                                                      Philip
-- 
Real programs don't eat cache (Malay)
-----------------------------------------------------------------------------
Philip Lijnzaad, lijnzaad_at_ebi.ac.uk \ European Bioinformatics Institute,rm A2-08
+44 (0)1223 49 4639                 / Wellcome Trust Genome Campus, Hinxton
+44 (0)1223 49 4468 (fax)           \ Cambridgeshire CB10 1SD,  GREAT BRITAIN
Received on Thu Sep 13 2001 - 18:28:25 CEST

Original text of this message