Print Story Parkdale sketch #5
Help!
By spacejack (Wed Jun 28, 2006 at 01:42:22 PM EST) Parkdale, postgres (all tags)
Also a question for postgres geniouses.


On King Street west of Dufferin:





And the postgres question: Anyone know how one would go about creating an array in a table for which each element must reference an ID of a field of another table?
< Get Hot or Go Home | BBC White season: 'Rivers of Blood' >
Parkdale sketch #5 | 12 comments (12 topical, 0 hidden) | Trackback
Is there a reason you can't just use a foreign key by Driusan (4.00 / 1) #1 Wed Jun 28, 2006 at 01:47:35 PM EST
Or does that expect the array as an object to reference another table (which doesn't make much sense to me) rather than each individual element? I'm not all that familiar with arrays in relational database.

--
Vive le Montréal libre.
I do want a foreign key by spacejack (2.00 / 0) #2 Wed Jun 28, 2006 at 01:51:56 PM EST
But I want an array of them :) Each record should be able to have any number of references to records in another table, not just one.

I've handled similar cases in the past by using another table, but I notice postgres has arrays and thought this might be a better way to handle it.

[ Parent ]
My solution would be: by Driusan (4.00 / 1) #5 Wed Jun 28, 2006 at 02:07:05 PM EST
1. Try just creating it with foo integer[] references blah.
2a. If that works, rejoice.
2b. If it fails, and you're feeling ambitious
2bi. look into the trigger that Postgres makes for a non-array foreign key. If any obvious way to modify that and make your own array version jumps out at you, try it.
3. If you're either feeling intelligent enough to realize it's not worth your time, or feeling lazy, then after 1, give up and use a join instead of an array.

I've never had much luck getting an array (with or without foreign keys) to work in Postgres, but to be honest I haven't tried that hard as my above flowchart might imply.

--
Vive le Montréal libre.

[ Parent ]
2a fails it by spacejack (2.00 / 0) #7 Wed Jun 28, 2006 at 02:11:29 PM EST
Not ambitious enough to try 2bi.

[ Parent ]
Fabian Pascal in da house by komet (4.00 / 3) #3 Wed Jun 28, 2006 at 01:54:07 PM EST
Arrays in relational dbs are evil. Do not use this Postgres feature; solve your problem the proper way, using an intermediate join table.

--
<ni> komet: You are functionally illiterate as regards trashy erotica.
I kind of figured that by spacejack (2.00 / 0) #4 Wed Jun 28, 2006 at 01:58:35 PM EST
But maybe there's an advocate of arrays who'll make a rebuttal... :)

[ Parent ]
I doubt it by komet (4.00 / 4) #6 Wed Jun 28, 2006 at 02:07:09 PM EST
Arrays are perhaps ok for a small number of values that are naturally arrays. For example, I have seen PG databases store coordinates, vectors and matrices as arrays. But if anyone has ever advocated putting keys in an array (and I haven't ever heard of such), surely Fabian Pascal and zombie Codd have hunted them down and killed them by now.

--
<ni> komet: You are functionally illiterate as regards trashy erotica.
[ Parent ]
alas ... by BlueOregon (4.00 / 1) #8 Wed Jun 28, 2006 at 02:42:55 PM EST

... google returns no results for "gallery of bad database design" or "museum of bad database design" and "bad database design" returns too many results, but "worst database design" provide a few nice gripes. It is a shame that "evil database design" is also empty, so all I could do was return to the Fabian Pascal Vociferous Ignorance Hall of Shame.

[ Parent ]
The Daily WTF... by crux (4.00 / 1) #12 Thu Jun 29, 2006 at 08:30:26 AM EST
... has database stuff ocassionally, here.

[ Parent ]
Day-yam! by greyrat (4.00 / 2) #9 Wed Jun 28, 2006 at 03:44:53 PM EST
Love the clouds especially! Did the cops come by and say "Get up off the street and move along you bum!"

They were busy by spacejack (4.00 / 1) #10 Wed Jun 28, 2006 at 04:10:07 PM EST
sorting out a fight half a block away.

[ Parent ]
Whatever was going on by calla (4.00 / 3) #11 Wed Jun 28, 2006 at 05:24:46 PM EST
contributed to the sketch.

Great technique and superb execution!!!

"Are Linux chicks worth it?" fencepost

[ Parent ]
Parkdale sketch #5 | 12 comments (12 topical, 0 hidden) | Trackback