Thread: arrays of foreign keys

arrays of foreign keys

From
Max
Date:
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.

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

Thanks for any help.

Max


Re: arrays of foreign keys

From
David Fetter
Date:
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

Re: arrays of foreign keys

From
Michael Glaesemann
Date:
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



Re: arrays of foreign keys

From
Ron Johnson
Date:
-----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-----

Re: arrays of foreign keys

From
"Albe Laurenz"
Date:
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

Re: arrays of foreign keys

From
Josh Trutwin
Date:
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

Re: arrays of foreign keys

From
Max
Date:
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