Maintaining a join table (PHP)

Report · Jan 08, 2008

I am writing code to maintain a join table linking products to accessories.
This relationship can be many to many.

So - when a product is updated, is it better to DELETE all join records and
then INSERT the new ones, or to only insert/update the ones that have
changed? The latter option seems a bit more complex to me.

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
==================

Report · Jan 08, 2008

> So - when a product is updated, is it better to DELETE all join records
> and
> then INSERT the new ones, or to only insert/update the ones that have
> changed? The latter option seems a bit more complex to me.

It's probably more of a UI issue.

If the screen to update the relation is where you update ALL of the
relations at once, I'd say deleting the old ones first, then putting them
all back in makes the most pragmatic sense.

Otherwise if the interface is a one-by-one type of thing, where you
edit/delete each relationship individually, then you would do the latter.

-Darrel

Report · Jan 08, 2008

It's an all in one screen. You can define up to 6 associated products for
each product ID, e.g.,

SKU = 11111
Assoc1 = 22222, Assoc2 = 33333, etc.

So, I guess I'll loop through the recordset, delete each, and then insert
the new values.

Thanks!

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
==================

"darrel" <notreal@nowhere.com> wrote in message
news:fm0jcn$gej$1@forums.macromedia.com...
>
>> So - when a product is updated, is it better to DELETE all join records
>> and
>> then INSERT the new ones, or to only insert/update the ones that have
>> changed? The latter option seems a bit more complex to me.
>
> It's probably more of a UI issue.
>
> If the screen to update the relation is where you update ALL of the
> relations at once, I'd say deleting the old ones first, then putting them
> all back in makes the most pragmatic sense.
>
> Otherwise if the interface is a one-by-one type of thing, where you
> edit/delete each relationship individually, then you would do the latter.
>
> -Darrel
>

Report · Jan 09, 2008

> It's an all in one screen. You can define up to 6 associated products for
> each product ID, e.g.,

If that's a hard variable (the 6) then you don't really need the 3rd table,
really.

Not that it hurts, of course...especially if they change their mind later.
;o)

So, yea, if it's an all at once thing, it's easier to just wipe out the old
settings first, and put the new ones back in.

So, DELETE FROM relationTable WHERE parentProductID = #

Then insert the new relations one-by-one back into the table.

-Darrel

Report · Jan 09, 2008

Thanks, darrel!

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
==================

"darrel" <notreal@nowhere.com> wrote in message
news:fm32s8$beq$1@forums.macromedia.com...
>> It's an all in one screen. You can define up to 6 associated products
>> for each product ID, e.g.,
>
> If that's a hard variable (the 6) then you don't really need the 3rd
> table, really.
>
> Not that it hurts, of course...especially if they change their mind later.
> ;o)
>
> So, yea, if it's an all at once thing, it's easier to just wipe out the
> old settings first, and put the new ones back in.
>
> So, DELETE FROM relationTable WHERE parentProductID = #
>
> Then insert the new relations one-by-one back into the table.
>
> -Darrel
>