Design Question - Used Lookup

customaware

This is a question seeking ideas of how others address this issue.

Suppose you have a whole heap of LookUp Business Objects. ie. Units

You use these as Peer Single references in other BOs.

When you look at a Query of Units you want a Delete operation ONLY on the instances that ARE NOT used anywhere else.

I have looked at....

Maintaining a Used Flag in the BO - Maintenance overhead
Maintaining a Used Count in the BO - Even more maintenance overhead
Using a matching attribute and testing for UNDEFINED. Does not seem to work

Interested to hear how other handle this.

BLOMASKY

How about if you create a new table with 3 attributes, BOName, ID, UnitsID
Every time you create a relationship in the other tables, you add a record to this table. (Yea, lot of work!)
Then, easy to check to see if existing record in this table for a unit.

2nd, choice, if your SQL back end supports foreign keys, create one in each BO that references this table, then the backend will stop any deletions. (would be easy to call a SP to do the delete and return true of fail.

Bruce

tford

Maintaining a Used Count in the BO - Even more maintenance overhead

If you are concerned about the overhead of a rule on the Unit BO, would it work to have a count attribute that on the Unit BO that is updated only by a process rule that runs the query after updating the count.

customaware

I have tried both of the approaches below Tom and in both instances we get into an infinite loop. And I cannot understand why

BO: TrainingType
Attributes:

pm_CountryTraining TYPE CountryTraining Peer Multiple
TrainingType TYPE Plain Text
UsedCount TYPE Number

Rules on TrainingType BO:
Option 1:
If CountryTraining WAS ADDED TO TrainingType.pm_CountryTraining OR CountryTraining WAS REMOVED FROM TrainingType.pm_CountryTraining Then
TrainingType.UsedCount=COUNT CountryTraining WHERE (CountryTraining IN TrainingType.pm_CountryTraining)
Option 2:
If TrainingType.pm_CountryTraining WAS CHANGED Then
TrainingType.UsedCount=COUNT CountryTraining WHERE (CountryTraining IN TrainingType.pm_CountryTraining)

The BO: CountryTraining has a Peer Single reference (matching reference) to TrainingType

customaware

Found the reason for the infinite loop. Rogue process being called from referenced BO.

Option 1 works and is fast.

If CountryTraining WAS ADDED TO TrainingType.pm_CountryTraining OR CountryTraining WAS REMOVED FROM TrainingType.pm_CountryTraining Then
TrainingType.UsedCount=COUNT CountryTraining WHERE (CountryTraining IN TrainingType.pm_CountryTraining)

tford

Glad you got it sorted out, Mark. I stared at this for quite a while last night wondering why it could cause a loop & kept coming up blank.