pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAntw: many-many mapping between unique tables
8+ messages / 4 participants
[nested] [flat]
* Antw: many-many mapping between unique tables
@ 2000-10-16 06:57 Gerhard Dieringer <DieringG@eba-haus.de>
0 siblings, 2 replies; 8+ messages in thread
From: Gerhard Dieringer @ 2000-10-16 06:57 UTC (permalink / raw)
To: indraneel@www.cdfd.org.in; +Cc: pgsql-sql
>>> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Antw: many-many mapping between unique tables
@ 2000-10-16 08:09 Gerhard Dieringer <DieringG@eba-haus.de>
0 siblings, 1 reply; 8+ messages in thread
From: Gerhard Dieringer @ 2000-10-16 08:09 UTC (permalink / raw)
To: indraneel@www.cdfd.org.in; +Cc: pgsql-sql
>>> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Antw: many-many mapping between unique tables
@ 2000-10-16 15:14 KuroiNeko <evpopkov@carrier.kiev.ua>
parent: Gerhard Dieringer <DieringG@eba-haus.de>
1 sibling, 0 replies; 8+ messages in thread
From: KuroiNeko @ 2000-10-16 15:14 UTC (permalink / raw)
To: pgsql-sql
> > 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)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Antw: many-many mapping between unique tables
@ 2000-10-16 15:17 Josh Berkus <josh@agliodbs.com>
parent: Indraneel Majumdar <indraneel@www.cdfd.org.in>
0 siblings, 1 reply; 8+ messages in thread
From: Josh Berkus @ 2000-10-16 15:17 UTC (permalink / raw)
To: pgsql-sql
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 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Antw: many-many mapping between unique tables
@ 2000-10-16 20:13 Indraneel Majumdar <indraneel@www.cdfd.org.in>
parent: Gerhard Dieringer <DieringG@eba-haus.de>
1 sibling, 0 replies; 8+ messages in thread
From: Indraneel Majumdar @ 2000-10-16 20:13 UTC (permalink / raw)
To: Gerhard Dieringer <DieringG@eba-haus.de>; +Cc: pgsql-sql
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 #
`************************************************************************/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Antw: many-many mapping between unique tables
@ 2000-10-16 21:39 Indraneel Majumdar <indraneel@www.cdfd.org.in>
0 siblings, 0 replies; 8+ messages in thread
From: Indraneel Majumdar @ 2000-10-16 21:39 UTC (permalink / raw)
To: Gerhard Dieringer <DieringG@eba-haus.de>; +Cc: pgsql-sql
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 #
`************************************************************************/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Antw: many-many mapping between unique tables
@ 2000-10-16 21:54 Indraneel Majumdar <indraneel@www.cdfd.org.in>
parent: Gerhard Dieringer <DieringG@eba-haus.de>
0 siblings, 1 reply; 8+ messages in thread
From: Indraneel Majumdar @ 2000-10-16 21:54 UTC (permalink / raw)
To: pgsql-sql
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 #
`************************************************************************/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Antw: many-many mapping between unique tables
@ 2000-10-17 05:19 Indraneel Majumdar <indraneel@www.cdfd.org.in>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 8+ messages in thread
From: Indraneel Majumdar @ 2000-10-17 05:19 UTC (permalink / raw)
To: pgsql-sql
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 #
`************************************************************************/
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2000-10-17 05:19 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-10-16 06:57 Antw: many-many mapping between unique tables Gerhard Dieringer <DieringG@eba-haus.de>
2000-10-16 15:14 ` KuroiNeko <evpopkov@carrier.kiev.ua>
2000-10-16 20:13 ` Indraneel Majumdar <indraneel@www.cdfd.org.in>
2000-10-16 08:09 Re: Antw: many-many mapping between unique tables Gerhard Dieringer <DieringG@eba-haus.de>
2000-10-16 21:54 ` Indraneel Majumdar <indraneel@www.cdfd.org.in>
2000-10-16 15:17 ` Josh Berkus <josh@agliodbs.com>
2000-10-17 05:19 ` Indraneel Majumdar <indraneel@www.cdfd.org.in>
2000-10-16 21:39 Re: Antw: many-many mapping between unique tables Indraneel Majumdar <indraneel@www.cdfd.org.in>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox