Preventing duplicate sys_user_grmember records

[David Sloan]

I have seen a lot of duplicate sys_user_grmember records where the same user is added to the same group more than once. I was thinking about creating a business rule to prevent this, and then I started thinking about adding a database index (on both fields) instead. Does anyone see a reason not to do one or the other of these approaches?

Here's what I was thinking for a business rule:

Or as a database index:

[jMarshal]

I don't think you want to index either of those fields on that table, both have many to many relationships with each other.

Do you know how you are getting these duplicate entries? Do you have background processes that create/populate the groups?

[David Sloan]

Not an index on either field, but a multi-column index that would prevent the two fields from being set to the same combination as elsewhere in the table.

Duplicate entries have been caused by employees adding a record that already exists, so it is a manual process that is the problem.

[Bert_c1 · Accepted solution]

Hi David,

I would use a business rule to prevent "duplicates" based on why you say "duplicates" are created, by users. I would not create a new index on that table.

You can include and "addInfoMessage() in a BR to let the user know there is a duplicate and abort the transaction too there.