Thread: Re: Foreign Key for PHP serialized data - possible?

Re: Foreign Key for PHP serialized data - possible?

From

Hannes Dorbath

Date:

06 October 2007, 21:55:03

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

Re: Foreign Key for PHP serialized data - possible?

From

"Dave"

Date:

08 October 2007, 16:41:29

"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.

Re: Foreign Key for PHP serialized data - possible?

From

Michael Glaesemann

Date:

08 October 2007, 17:09:18

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

Re: Foreign Key for PHP serialized data - possible?

From

Richard Huxton

Date:

08 October 2007, 17:17:30

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

Re: Foreign Key for PHP serialized data - possible?

From

Erik Jones

Date:

08 October 2007, 18:00:09

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