pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedForeign Key for multi PK or design question
7+ messages / 4 participants
[nested] [flat]
* Foreign Key for multi PK or design question
@ 2007-12-11 18:20 PostgreSQL Admin <postgres@productivitymedia.com>
0 siblings, 2 replies; 7+ messages in thread
From: PostgreSQL Admin @ 2007-12-11 18:20 UTC (permalink / raw)
To: pgsql-sql
I have a table in which people will have a number of questions to
answer. I want those pk to be placed in my user table. So if a user
answers three question I want those 3 pk's in the user table (fk).
What should I be doing?
Thanks in advance,
J
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Foreign Key for multi PK or design question
@ 2007-12-11 18:35 Erik Jones <erik@myemma.com>
parent: PostgreSQL Admin <postgres@productivitymedia.com>
1 sibling, 0 replies; 7+ messages in thread
From: Erik Jones @ 2007-12-11 18:35 UTC (permalink / raw)
To: PostgreSQL Admin <postgres@productivitymedia.com>; +Cc: pgsql-sql
On Dec 11, 2007, at 12:20 PM, PostgreSQL Admin wrote:
> I have a table in which people will have a number of questions to
> answer. I want those pk to be placed in my user table. So if a user
> answers three question I want those 3 pk's in the user table (fk).
> What should I be doing?
You're going to have to give a more concrete example of what it is
you're trying to do, i.e what those questions are, table structures,
etc.
Erik Jones
Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Foreign Key for multi PK or design question
@ 2007-12-11 18:53 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: PostgreSQL Admin <postgres@productivitymedia.com>
1 sibling, 1 reply; 7+ messages in thread
From: A. Kretschmer @ 2007-12-11 18:53 UTC (permalink / raw)
To: pgsql-sql
am Tue, dem 11.12.2007, um 13:20:52 -0500 mailte PostgreSQL Admin folgendes:
> I have a table in which people will have a number of questions to
> answer. I want those pk to be placed in my user table. So if a user
> answers three question I want those 3 pk's in the user table (fk).
> What should I be doing?
Explain your problem a little bit more. We need your table-design, for
instance. Nobody can help you without more informations. Maybe you are
searching for current_user, but i'm not sure.
Btw.:
Please don't hijack other threads, this problems was a topic on this¹
list today. Your email contains a References: - Header, and you are
using Thunderbird. I think, you can understand me what i mean.
If yo want to create a new topic, create a *new* mail and don't answer
to an existing mail by changing the subject.
¹ it was the [General]-List, sorry
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Foreign Key for multi PK or design question
@ 2007-12-11 19:12 PostgreSQL Admin <postgres@productivitymedia.com>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 2 replies; 7+ messages in thread
From: PostgreSQL Admin @ 2007-12-11 19:12 UTC (permalink / raw)
To: pgsql-sql
This is my layout so far:
CREATE TABLE users (
id serial NOT NULL,
--question REFERENCES questions(id) ON DELETE CASCADE ## ON REMOVED##
);
CREATE TABLE questions (
id serial NOT NULL,
questions varchar(450) NOT NULL
);
CREATE TABLE answers (
id serial NOT NULL,
question_id int REFERENCES questions(id) ON DELETE CASCADE,
user_id int REFERENCES users(id) ON DELETE CASCADE,
answer varchar(450) NOT NULL,
created timestamptz NOT NULL
);
Originally I wanted to have a foreign key that would be the pk of the
question table. So if the user answered Q2, 5 and 6 - the user.fk would
store values 2,5,6 - but I have passed most of logic to the answer table.
Does this look correct? or most efficient?
J
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Foreign Key for multi PK or design question
@ 2007-12-11 19:42 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: PostgreSQL Admin <postgres@productivitymedia.com>
1 sibling, 0 replies; 7+ messages in thread
From: Alvaro Herrera @ 2007-12-11 19:42 UTC (permalink / raw)
To: PostgreSQL Admin <postgres@productivitymedia.com>; +Cc: pgsql-sql
PostgreSQL Admin wrote:
> This is my layout so far:
>
> CREATE TABLE users (
> id serial NOT NULL,
> --question REFERENCES questions(id) ON DELETE CASCADE ## ON REMOVED##
> );
>
> CREATE TABLE questions (
> id serial NOT NULL,
> questions varchar(450) NOT NULL
> );
>
> CREATE TABLE answers (
> id serial NOT NULL,
> question_id int REFERENCES questions(id) ON DELETE CASCADE,
> user_id int REFERENCES users(id) ON DELETE CASCADE,
> answer varchar(450) NOT NULL,
> created timestamptz NOT NULL
> );
>
> Originally I wanted to have a foreign key that would be the pk of the
> question table. So if the user answered Q2, 5 and 6 - the user.fk would
> store values 2,5,6 - but I have passed most of logic to the answer table.
That would have made no sense.
> Does this look correct? or most efficient?
Yeah it seems sane, however you have forgotten to add NOT NULL to the FK
fields.
--
Alvaro Herrera http://www.PlanetPostgreSQL.org/
"No me acuerdo, pero no es cierto. No es cierto, y si fuera cierto,
no me acuerdo." (Augusto Pinochet a una corte de justicia)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Foreign Key for multi PK or design question
@ 2007-12-11 19:48 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: PostgreSQL Admin <postgres@productivitymedia.com>
1 sibling, 1 reply; 7+ messages in thread
From: A. Kretschmer @ 2007-12-11 19:48 UTC (permalink / raw)
To: pgsql-sql
am Tue, dem 11.12.2007, um 14:12:38 -0500 mailte PostgreSQL Admin folgendes:
> This is my layout so far:
> [ table-layout]
> ...
> Does this look correct?
Yes, why not? Do you have problems? Which? I can't see problems, it's a
normalized design IMHO.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Foreign Key for multi PK or design question
@ 2007-12-11 20:04 PostgreSQL Admin <postgres@productivitymedia.com>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 0 replies; 7+ messages in thread
From: PostgreSQL Admin @ 2007-12-11 20:04 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-sql
No problems with the design - I was not thinking with the DB hat on at
first. I have been working on clustering for a while... just adjusting.
Thanks everyone.
:)
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-12-11 20:04 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-11 18:20 Foreign Key for multi PK or design question PostgreSQL Admin <postgres@productivitymedia.com>
2007-12-11 18:35 ` Erik Jones <erik@myemma.com>
2007-12-11 18:53 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-12-11 19:12 ` PostgreSQL Admin <postgres@productivitymedia.com>
2007-12-11 19:42 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2007-12-11 19:48 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-12-11 20:04 ` PostgreSQL Admin <postgres@productivitymedia.com>
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