Re: Foreign Key for PHP serialized data - possible?

Dave wrote:
> "Hannes Dorbath" <light(at)theendofthetunnel(dot)de> wrote in message
> news:4707FFF6(dot)7010402(at)theendofthetunnel(dot)de(dot)(dot)(dot)
>> Dave wrote:
>>> e.g. if an id of Oranges changes from '5' to '24', the tb1_column rows
>>> will
>>> get changed in the above example row to:
>>> a:5:{i:0;s:1:"9";i:1;s:2:"24";i:2;s:2:"11";i:3;s:2:"100";i:4;s:2:"10";}
>> No offense, but that sounds like sick application design. Anyway, a
>> plphp trigger can do it:

>> No offense, but that sounds like sick application design
> Offense taken :)
> Well, you don't know the whole picture, so I can see why this would look bad
> to you.

I don't know the whole picture either, but I'll side with Hannes because
I can't think of any circumstance where it's a good idea.

> That's why asked any other ideas.
> The problem I need to solve is this:
>
> I have main table with lets say:
>
> 11 | Pears
> 100 | Plums
> 9 | Apples
> 5 | Oranges
> 10 | Cranberries

OK

> Now, I need to create another table with options for each of the above. So
> for the apples:
> ID | colors_available | favourite_color |
> kind | favourite kind | Other similar options ...
> 9 | red, green, yellow, ...infinity | red | Granny smith, Golden delicious,
> ... infinity | Granny smith | Other similar values
>
> What I'm trying to do is to keep the options for each froot in one row, as
> opposed to creating separate table for colors, kind, etc.

Why?

> I realize that I could create separate tables for colors, kind, etc, and
> separate tables for favourite colors and favourite kind, but this would
> involve accessing four different tables in this case.

Oh no! Accessing multiple tables in a relational database, madness!

> I guess, I can always
> set up views with joins, but could not come up with a good way to query this
> in a one returned row, e.g.:
> ID | Name | colors | fav_col | kind
> | fav_kind | etc.
> 11 | Apples | red, green, etc. | red | Granny smith, Golden
> delicious.. | Granny smith | etc.

If you don't like tha array_accum() option described in the manuals, you
can build a more direct text-aggregator.

CREATE OR REPLACE FUNCTION agg_text(text, text) RETURNS text AS
$$
SELECT CASE
WHEN ($1 = '') THEN $2
ELSE $1 || ', ' || $2
END;
$$ LANGUAGE 'SQL' IMMUTABLE;

CREATE AGGREGATE agg_text (sfunc1=agg_text, basetype=text, stype1=text,
initcond1='');

SELECT group_column, agg_text(my_text_column) FORM my_table GROUP BY
group_column;

--
Richard Huxton
Archonet Ltd