Thread: Antw: many-many mapping between unique tables

Antw: many-many mapping between unique tables

From
"Gerhard Dieringer"
Date:
>>> Indraneel Majumdar <indraneel@www.cdfd.org.in> 16.10.2000  20.11 Uhr >>>
> Hi,
>
> I am facing a problem in mapping between two tables containing unique
> entries
>
> T1        T2
> _________    ________
> |  x1    |    |  y1    |
> |  x2    |    |  y2    |
> |  x3    |    |  y3    |
> ---------    ---------
>
> x(i) points to 1 or more entries in T2. y(i) points to one or more entries
> in T1. How do I store this mapping? I'd cannot use an array due to size
> restrictions and inability to extract data easily for furthur processing.
> 
> I don't want to put everything into a single table and repeat values in
> the 2nd column since these are really huge tables and I cannot increase
> overhead by increasing data redundancy.
> 
> Is there some way to do something about this?
>
> \Indraneel
> 
> /************************************************************************.
> # Indraneel Majumdar                  ¡  E-mail: indraneel@123india.com  #
> # Bioinformatics Unit (EMBNET node),  ¡  URL: http://scorpius.iwarp.com  #
> # Centre for DNA Fingerprinting and Diagnostics,                         #
> # Hyderabad, India - 500076                                              #
> `************************************************************************/

I'm not sure if I understand your question. Is it right, that the x(i) are different values
of an column x in table T1 and the y(i) are different values of an column y in table T2?
And you want to have a n:m relation between the two tables?
Then you need a third table T12 with columns x and y, that contains one row for each
mapping of a x(i) with a y(j).

I hope this helps.


Gerhard







Re: Antw: many-many mapping between unique tables

From
Indraneel Majumdar
Date:
Yeah you understand the problem perfectly. The third table will definitely
contain unique entries if you take both x and y simultaneously. I wanted
some way of mapping where I would not need to repeat individual values of
x and y. I am not from a maths background and have absolutely no idea of
database design.

Is it possible to map the columns without repeating values?

\Indraneel

On Mon, 16 Oct 2000, Gerhard Dieringer wrote:

> I'm not sure if I understand your question. Is it right, that the x(i) are different values
> of an column x in table T1 and the y(i) are different values of an column y in table T2?
> And you want to have a n:m relation between the two tables?
> Then you need a third table T12 with columns x and y, that contains one row for each
> mapping of a x(i) with a y(j).
> 
> I hope this helps.
> 
> 
> Gerhard

/************************************************************************.
# Indraneel Majumdar                  ¡  E-mail: indraneel@123india.com  #
# Bioinformatics Unit (EMBNET node),  ¡  URL: http://scorpius.iwarp.com  #
# Centre for DNA Fingerprinting and Diagnostics,                         #
# Hyderabad, India - 500076                                              #
`************************************************************************/



Re: Antw: many-many mapping between unique tables

From
"Gerhard Dieringer"
Date:
>>> Indraneel Majumdar <indraneel@www.cdfd.org.in> 16.10.2000  22.13 Uhr >>>
> Yeah you understand the problem perfectly. The third table will definitely
> contain unique entries if you take both x and y simultaneously. I wanted
> some way of mapping where I would not need to repeat individual values of
> x and y. I am not from a maths background and have absolutely no idea of
> database design.
>
> Is it possible to map the columns without repeating values?
> 
> \Indraneel

I don't know what type of data your columns x and y contain. If they are,
for example, of type text and contain long strings, then it would be better
to add an integer id column in each tables T1 and T2: x_id and y_id and
use these ids in the table T12.

Then you only have to repeat the (short) numeric vales in T12. But I think there 
is no way to totaly avoid any repetition.

--------------
Gerhard



JDBC Performance

From
Craig May
Date:
Hi,

I've been using the postgresql.Driver with JDBC for some time. 

Initially, I was passing Statement objects through functions to each of the
Classes.

This was working beautifully. 

I have since adapted my application to support MS SQL Server.

SQL Server doesn't allow a statement object to perform multiple queries, so i
have changed the application to create additional Connections.

This has reduced it's performance around 400%.

I'm using the driver contained with Postgresql 7.1

Does anyone have ideas on increasing performance with multiple connections?

Kind Regards,
Craig May

Enth Dimension
http://www.enthdimension.com.au



Re: Antw: many-many mapping between unique tables

From
Indraneel Majumdar
Date:
Thanks, I too came to the same conclusion (due to lack of
persistence/patience).

Thanks,
Indraneel

On Mon, 16 Oct 2000, Gerhard Dieringer wrote:

> I don't know what type of data your columns x and y contain. If they are,
> for example, of type text and contain long strings, then it would be better
> to add an integer id column in each tables T1 and T2: x_id and y_id and
> use these ids in the table T12.
> 
> Then you only have to repeat the (short) numeric vales in T12. But I think there 
> is no way to totaly avoid any repetition.
> 
> --------------
> Gerhard
> 

/************************************************************************.
# Indraneel Majumdar                  ¡  E-mail: indraneel@123india.com  #
# Bioinformatics Unit (EMBNET node),  ¡  URL: http://scorpius.iwarp.com  #
# Centre for DNA Fingerprinting and Diagnostics,                         #
# Hyderabad, India - 500076                                              #
`************************************************************************/



Re: Antw: many-many mapping between unique tables

From
Indraneel Majumdar
Date:
Sorry for splitting mails. Is there some way of using the oid of each
row to do the mapping instead of creating two more columns of integers?
The third table can then be like this:

create table T3(T1    oid,T2    oid
);

also how can I find out the oid of a particular row? and how can I use
this oid in a select statement?

Thanks,
Indraneel

On Mon, 16 Oct 2000, Gerhard Dieringer wrote:

> I don't know what type of data your columns x and y contain. If they are,
> for example, of type text and contain long strings, then it would be better
> to add an integer id column in each tables T1 and T2: x_id and y_id and
> use these ids in the table T12.
> 
> Then you only have to repeat the (short) numeric vales in T12. But I think there 
> is no way to totaly avoid any repetition.
> 
> --------------
> Gerhard
> 

/************************************************************************.
# Indraneel Majumdar                  ¡  E-mail: indraneel@123india.com  #
# Bioinformatics Unit (EMBNET node),  ¡  URL: http://scorpius.iwarp.com  #
# Centre for DNA Fingerprinting and Diagnostics,                         #
# Hyderabad, India - 500076                                              #
`************************************************************************/



Re: Antw: many-many mapping between unique tables

From
Josh Berkus
Date:
Mr. Majumdar,
> 
> Sorry for splitting mails. Is there some way of using the oid of each
> row to do the mapping instead of creating two more columns of integers?
> The third table can then be like this:
> 
> create table T3(
>         T1      oid,
>         T2      oid
> );

Please reference two things:

1. Look in the list archives for the last 4 weeks and you will read a
long discussion on the risks/limitations of using an OID as the primary
key on very large tables. (Subject: Object-Relational Database Design).

2. For how to use OIDs, please look in Bruce Momjian's excellent PGSQL
book-in-progress, available on the Postgresql.org website. (Chapter 7, I
believe).
                -Josh Berkus

-- 
______AGLIO DATABASE SOLUTIONS___________________________                                       Josh Berkus  Complete
informationtechnology      josh@agliodbs.com   and data management solutions       (415) 436-9166  for law firms, small
businesses      fax  436-0137   and non-profit organizations.       pager 338-4078                               San
Francisco


Re: Antw: many-many mapping between unique tables

From
KuroiNeko
Date:
> > I am facing a problem in mapping between two tables containing unique
> > entries
> >
> Then you need a  third table T12 with columns x and  y, that contains one
> row for each
> mapping of a x(i) with a y(j).
That's it.  Normalize it. Relation T12  may also require the  third field,
`direction', to specify whether  this is T1 -> T2 or T2  -> T1. In general,
it'd be much better to re-design data layout.


--
Well I tried to be meek And I have tried to be mildBut I spat like a woman And I sulked like a childI have lived behind
thewalls That have made me aloneStriven for peace Which I never have known
 
Dire Straits, Brothers In Arms, The Man's Too Strong (Knopfler)



Re: Antw: many-many mapping between unique tables

From
Indraneel Majumdar
Date:
Hi,

Thank you very much. Now I know to use serial and not oid. I hope I got
the correct message.

Thanks,
Indraneel

On Mon, 16 Oct 2000, Josh Berkus wrote:

> Please reference two things:
> 
> 1. Look in the list archives for the last 4 weeks and you will read a
> long discussion on the risks/limitations of using an OID as the primary
> key on very large tables. (Subject: Object-Relational Database Design).
> 
> 2. For how to use OIDs, please look in Bruce Momjian's excellent PGSQL
> book-in-progress, available on the Postgresql.org website. (Chapter 7, I
> believe).
> 

/************************************************************************.
# Indraneel Majumdar                  ¡  E-mail: indraneel@123india.com  #
# Bioinformatics Unit (EMBNET node),  ¡  URL: http://scorpius.iwarp.com  #
# Centre for DNA Fingerprinting and Diagnostics,                         #
# Hyderabad, India - 500076                                              #
`************************************************************************/



Re: JDBC Performance

From
Josh Berkus
Date:
Mr. May,
For discussions of JDBC, please subscribe to the pgsql-interfaces
list.  You will find many JDBC users on that list.
                -Josh Berkus

P.S. PGSQL folks, is there any way we can clarify this on the web page? 
The JDBC users seem to keep ending up on this list.

-- 
______AGLIO DATABASE SOLUTIONS___________________________                                       Josh Berkus  Complete
informationtechnology      josh@agliodbs.com   and data management solutions       (415) 436-9166  for law firms, small
businesses      fax  436-0137   and non-profit organizations.       pager 338-4078                               San
Francisco


Re: JDBC Performance

From
Peter Mount
Date:
On Mon, 16 Oct 2000, Josh Berkus wrote:

> Mr. May,
> 
>     For discussions of JDBC, please subscribe to the pgsql-interfaces
> list.  You will find many JDBC users on that list.
> 
>                     -Josh Berkus
> 
> P.S. PGSQL folks, is there any way we can clarify this on the web page? 
> The JDBC users seem to keep ending up on this list.

I think it needs to be mentioned somewhere on the postgresql site. I've
had this on my site for the last 3 years ;-)

Also, I only get the sql & general lists here at home (retep.org.uk), but
get interfaces at work as well so I can sometimes answer it faster if it's
posted to interfaces.

Peter

-- 
Peter T Mount peter@retep.org.uk http://www.retep.org.uk
PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/
Java PDF Generator http://www.retep.org.uk/pdf/