Antw: [SQL] many-many mapping between unique tables
>>> Indraneel Majumdar <[EMAIL PROTECTED]> 16.10.2000 20.11 Uhr >>> > Hi, > > I am facing a problem in mapping between two tables containing unique > entries > > T1T2 > _ > | 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: [EMAIL PROTECTED] # > # 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: [SQL] many-many mapping between unique tables
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: [EMAIL PROTECTED] # # Bioinformatics Unit (EMBNET node), ¡ URL: http://scorpius.iwarp.com # # Centre for DNA Fingerprinting and Diagnostics, # # Hyderabad, India - 500076 # `/
Re: Antw: [SQL] many-many mapping between unique tables
>>> Indraneel Majumdar <[EMAIL PROTECTED]> 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
[SQL] JDBC Performance
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: [SQL] many-many mapping between unique tables
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: [EMAIL PROTECTED] # # Bioinformatics Unit (EMBNET node), ¡ URL: http://scorpius.iwarp.com # # Centre for DNA Fingerprinting and Diagnostics, # # Hyderabad, India - 500076 # `/
Re: Antw: [SQL] many-many mapping between unique tables
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: [EMAIL PROTECTED] # # Bioinformatics Unit (EMBNET node), ¡ URL: http://scorpius.iwarp.com # # Centre for DNA Fingerprinting and Diagnostics, # # Hyderabad, India - 500076 # `/
Re: Antw: [SQL] many-many mapping between unique tables
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 information technology [EMAIL PROTECTED] 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: [SQL] many-many mapping between unique tables
> > 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 mild But I spat like a woman And I sulked like a child I have lived behind the walls That have made me alone Striven for peace Which I never have known Dire Straits, Brothers In Arms, The Man's Too Strong (Knopfler)
Re: Antw: [SQL] many-many mapping between unique tables
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: [EMAIL PROTECTED] # # Bioinformatics Unit (EMBNET node), ¡ URL: http://scorpius.iwarp.com # # Centre for DNA Fingerprinting and Diagnostics, # # Hyderabad, India - 500076 # `/
[SQL] Formatting US phone numbers, was Variable-length Types
Josh and anyone interested, I've slightly changed the function. C source of a standalone program is available from http://www.primechoice.com/hum/uspn.c Comments welcome. Thx Ed PS Lemme know if you need a function, callable from PGSQL -- Well I tried to be meek And I have tried to be mild But I spat like a woman And I sulked like a child I have lived behind the walls That have made me alone Striven for peace Which I never have known Dire Straits, Brothers In Arms, The Man's Too Strong (Knopfler)
[SQL] Re: many-to-many mapping
Am I the only one who's nervous about these two tables referencing each other like this? I would have a concern that this is being imposed, perhaps, by some external format that the data arrives in, and that a decently normalized design will have a simple all-key table containing nothing but serial # id's from both primary tables. Join 18 million Eudora users by signing up for a free Eudora Web-Mail account at http://www.eudoramail.com
Re: [SQL] JDBC Performance
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 information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
