Thread: Antw: many-many mapping between unique tables
>>> 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
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 # `************************************************************************/
>>> 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
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
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 # `************************************************************************/
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 # `************************************************************************/
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
> > 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)
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 # `************************************************************************/
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
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/