Обсуждение: Re: Foreign Key for PHP serialized data - possible?
Dave wrote:
> What I'm trying to do is to create a foreign key on tb1_column so that if a
> number in tb2_column changes or gets deleted, it cascades to the appropriate
> segment of the serialized data.
> 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:
http://www.commandprompt.com/community/plphp
--
Best regards,
Hannes Dorbath
"Hannes Dorbath" <light@theendofthetunnel.de> wrote in message
news:4707FFF6.7010402@theendofthetunnel.de...
> Dave wrote:
>> What I'm trying to do is to create a foreign key on tb1_column so that if
>> a
>> number in tb2_column changes or gets deleted, it cascades to the
>> appropriate
>> segment of the serialized data.
>> 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:
>
> http://www.commandprompt.com/community/plphp
>
>
> --
> Best regards,
> Hannes Dorbath
> 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. 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
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.
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. 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.
On Oct 8, 2007, at 8:33 , Dave wrote: > 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. You can either do it in your middleware or use array_accum. http://www.postgresql.org/docs/8.2/static/xaggr.html The archives have examples of doing things like this. Note that *this* is the problem you're trying to solve, not the serialization issue you're having above. I recommend changing your schema. Michael Glaesemann grzm seespotcode net
Dave wrote:
> "Hannes Dorbath" <light@theendofthetunnel.de> wrote in message
> news:4707FFF6.7010402@theendofthetunnel.de...
>> 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
On Oct 8, 2007, at 12:08 PM, Michael Glaesemann wrote: > > On Oct 8, 2007, at 8:33 , Dave wrote: > >> 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. > > You can either do it in your middleware or use array_accum. > > http://www.postgresql.org/docs/8.2/static/xaggr.html > > The archives have examples of doing things like this. > > Note that *this* is the problem you're trying to solve, not the > serialization issue you're having above. I recommend changing your > schema. I, also, agree. The "problem" you're trying to avoid with having separate tables for colors, kinds, etc. is exactly what a relational database is for. 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