Posted by ibiza on 02/16/06 20:39

Hi SQL fans,

I realized that I often encounter the same situation in a relation
database context, where I really don't know what to do. Here is an
example, where I have 2 tables as follow:


________________________
__________________ | PortfolioTitle
|
| Portfolio |
+----------------------------------------+
+-----------------------------+ | tfolio_id (int)
|
| folio_id (int) |<<-PK----FK--| tfolio_idfolio (int)
|
| folio_name (varchar) | | tfolio_idtitle (int)
|--FK----PK->>[ Titles]
+-----------------------------+ | tfolio_weight
(decimal(6,5)) |

+-----------------------------------------+


Note that I also have a "Titles" tables (hence the tfolio_idtitle
link).

My problem is : When I update a portfolio, I must update all the
associated titles in it. That means that titles can be either removed
from the portfolio (a folio does not support the title anymore), added
to it (a new title is supported by the folio) or simply updated (a
title stays in the portfolio, but has its weight changed)

For example, if the portfolio #2 would contain :

[ PortfolioTitle ]
id | idFolio | idTitre | poids
1 2 1 10
2 2 2 20
3 2 3 30

and I must update the PortfolioTitle based on these values :

idFolio | idTitre | poids
2 2 20
2 3 35
2 4 40

then I should
1 ) remove the title #1 from the folio by deleting its entry in the
PortfolioTitle table
2 ) update the title #2 (weight from 30 to 35)
3 ) add the title #4 to the folio

For now, the only way I've found to do this is delete all the entries
of the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio =
2), and then insert new values for each entry based on the new given
values.

Is there a way to better manage this by detecting which value has to be
inserted/updated/deleted?

And this applies to many situation :(

If you need other examples, I can give you.

thanks a lot!

ibiza

[Back to original message]