arrays of foreign keys

On Fri, Sep 07, 2007 at 11:47:40PM -0000, Max wrote:

Hello,

And pardon me if I posted this question to the wrong list, it seems
this list is the most appropriate.

I am trying to create a table with an array containing foreign keys.
I've searched through the documentation and couldn't find a way to
do so.

Is this something that one can do?

It may be, but it's a bad idea.

Basically, I have two tables:

create table user (
user_id serial,
login varchar(50) primary key,
tags integer[]-- this is where the problem lies

Yes, it's a design problem.

);

create table tag (
tag_id serial,
name varchar(50) primary key
);

I would like the user.tags field to be a list of foreign keys (tag_ids
specifically).

I know I can solve my problem with the following table:

create table user_tag (
user_id integer,
tag_id integer,
foreign key (user_id) references table user(user_id) on delete
cascade,
foreign key (tag_id) references table tag(tag_id) on delete cascade,
primary key (user_id,tag_id)
);

But I would really like to avoid doing that.

Why? It's good, standard, normalized design, and it will work just
fine. You can make a VIEW atop this one using array_accum(), and you
can even make that VIEW writeable if you come up with behavior for
INSERT, UPDATE and DELETE.

Is there a solution to this problem with arrays of foreign keys, and
if so, how does one do that?

See above :)

Cheers,
David.

Thanks for any help.

Max

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate

On Sep 7, 2007, at 18:47 , Max wrote:

I am trying to create a table with an array containing foreign keys.
I've searched through the documentation and couldn't find a way to do
so.

It's because this is not how relational databases are designed to
work. From the server's point of view, an array is an opaque
structure, to be treated as a whole. You're working against the
system here.

I know I can solve my problem with the following table:

And this is how you should do it.

Michael Glaesemann
grzm seespotcode net

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 09/07/07 18:47, Max wrote:

Hello,

And pardon me if I posted this question to the wrong list, it seems
this list is the most appropriate.

I am trying to create a table with an array containing foreign keys.
I've searched through the documentation and couldn't find a way to do
so.

Is this something that one can do?

Basically, I have two tables:

create table user (
user_id serial,
login varchar(50) primary key,
tags integer[]-- this is where the problem lies
);

create table tag (
tag_id serial,
name varchar(50) primary key
);

I would like the user.tags field to be a list of foreign keys (tag_ids
specifically).

I know I can solve my problem with the following table:

create table user_tag (
user_id integer,
tag_id integer,
foreign key (user_id) references table user(user_id) on delete
cascade,
foreign key (tag_id) references table tag(tag_id) on delete cascade,
primary key (user_id,tag_id)
);

But I would really like to avoid doing that.

Why?

The (literally) cardinal rule of database normalization is
"eliminate repeating values".

Is there a solution to this problem with arrays of foreign keys, and
if so, how does one do that?

Thanks for any help.

- --
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFG5UDUS9HxQb37XmcRAo+WAKDSB8DNYBZiZtOJ3utAkkX9QkDYtACgk0ab
HKf6Oe1DbvZP8cmh1e9dZaQ=
=lPTU
-----END PGP SIGNATURE-----

Max wrote:

I am trying to create a table with an array containing foreign keys.
I've searched through the documentation and couldn't find a way to do
so.

Is this something that one can do?

Basically, I have two tables:

create table user (
user_id serial,
login varchar(50) primary key,
tags integer[]-- this is where the problem lies
);

create table tag (
tag_id serial,
name varchar(50) primary key
);

I would like the user.tags field to be a list of foreign keys (tag_ids
specifically).

Fortunately, this is not possible.

I know I can solve my problem with the following table:

create table user_tag (
user_id integer,
tag_id integer,
foreign key (user_id) references table user(user_id) on delete
cascade,
foreign key (tag_id) references table tag(tag_id) on delete cascade,
primary key (user_id,tag_id)
);

But I would really like to avoid doing that.

Why?

That is the correct and efficient solution of your problem.

Go for it!

Yours,
Laurenz Albe

On Fri, 07 Sep 2007 23:47:40 -0000
Max <mlavenne@gmail.com> wrote:

Hello,

And pardon me if I posted this question to the wrong list, it seems
this list is the most appropriate.

I am trying to create a table with an array containing foreign keys.
I've searched through the documentation and couldn't find a way to
do so.

Is this something that one can do?

Ok - now that you have 4 replies telling you NOT to do this, if you
really decide it's how you want to proceed - it can be done by
writing your own triggers.

Personally, I would heed the advise of the other replies and seriously
consider why you want to do this. If you decide to go against
standard normalized database practice (as is your right) feel free to
ping me off list and I can send you an example of what I've done.

Josh

Hello,

Thanks everyone for your input. Then, it sounds like I won't use an
array of foreign keys. I was just curious about the array
functionality.

However, I didn't think about setting up a view above the intermediary
table with an array_accum, now I have never heard of array_accum. I
did some research in the online doc. It's a cool functionality, but
what's the performance of it?

Would using an array_accum slow down a view?

Thanks

Max