Forum OpenACS Development: Why unique constraints should be treated with care

I know from the history of OpenACS that the system relied on unique e-mail address for login purposes, but now the system is expanding and is hindered by this constraint e.g. for a CRM setting. My experience has shown and hopefully others can jump in as well, that for CRM purposes e-mails are not unique. This is why most sites today ask for a username and not an e-mail because other systems already detected the fact that e-mails are not unique.

Example:

An aweful lot of companies use “info@companyname” addresses. So some of the employees have the info@... Address and some don’t (have their own one). Still they are separate users in the system.

I faced this situation now in three systems where I had to import data from the client. In the first installation I managed to get away with not adding the e-mail to the second person but state “mailto:other_party_id@foo.bar”. But in the other two setups we had around 4000 doublicates each. And sadly not always for the same company (otherwise I could have tricked the system and store the e-mail with the organization_id and rewrite any application to check for the e-mail of the organization if there is none for the party (which we actually do anyway)).

You could then argue that this is not a perfect scenario and the clients should clean up their data (which they will do eventually), but if some other users are going to use contacts to import customer CRM data they are in for a big surprise with the unique constraint on

e-mail
organization name

Did I mention unique constraint on organization name is a really bad idea? I know, in a perfect world a company name should only exist once. But guess what. World isn’t perfect :). Anyway, this is just another example where a developer has created a perfect setting not reflecting the realities in live. Sorry for getting started on this, obviously we dropped the unique constraint in organizations. Let the users find out which organization they want if they have multiple ones with the same name (which is easy to do if you use contacts as usually there is a second company_name or an address which helps differentiating).

Therefore I'd encourage developers to be *really* careful with unique constraints and make a check if this unique constraint is reflected in the real world. Usually it isn't and you should never code a unique constraint just because it makes your live easier.

- Overwrite in your custom package the party::get_id_by_email function as you obviously do with the altering of the table.

As we can do this in general I will not bother further (aka write a TIP) as the feeling from the OCT is at the moment that it is opposed to the idea.

OTOH as this is a required feature by many (as it seems), maybe we should write a package called "zz-email-not-unique" so others can install it to work around the custom behaviour in OpenACS.

in your example, make a group/community info, subscribe the people in question to it, and set the reply-to address for outgoing mails. doesn't do forums something similar already?

just my 2 cents...

Now let's get started 😊

Let's put it this way. I have three clients with user data of each around 20.000 customers with 40.000 contact people. Within these I find that we have around 1000 contacts who are using the same e-mail address. These are distinct people (read: parties).

The unique constraint on emails is on *parties*, which is a table, if I'm not mistaken, that has *nothing* to do with users, because groups and organizations could be parties as well. So, to answer your question: If two *PARTIES* have the same e-mail, I use *username* in my system to log them in. Which is my whole point. Do not use email for the login of *users* but username. Username is part of the users table, emails is part of the parties table. There are more parties than users in the system, so it does not make sense to limit the parties in my opinion due to the fact that you need a uniqueness on login, which you are giving to real person (aka users).

As for the Oracle delay, would you mind publicly stating which CVS commits from myself you refer to with regards to fixing Oracle? You keep on telling that I'm responsible for delaying the 5.2 release when I thought we established that the code change, though made after the code freeze and therefore a mistake on my behalf which I already apologized for, has not caused any delays in terms of broken code.

Taking that my memory might be bad in that the previous point I just wanted to make that the reason for this exchange (a three line code change...) has actually the capabilities of the dire consequences of breaking code which I wont be able to fix.

http://xarg.net/tools/cvs/change-set-details?key=22705

So, maybe I am exaggerating a little bit (which is different from hand waving, unless my command of the english language is not good enough). But I stand by my point that we have more unique constraints than we need.

It is not really necessary to understand how people collectively manage a resource, such as one email address --just that it happens all the time all over the world. Why do we need to know if people use 2+ clients for 1 email, or a web interface, filters and maybe a little cooperation in manually managing emails?

OpenACS should consider it part of the behavioral norm as much as any other community user variation.

Constraints for corporate/legal/privacy reasons does not hold. The first person who registers to an OpenACS site using a shared email results in similar circumstances to when multiple parties register with the same email. In each case, an email potentially destined to one person may be read by more than one. How can the site owners be responsible for that?

Certainly ecommerce should have strict requirements. But might it be reasonable to assume that those sharing an email might also be sharing other private info?

How to enforce privacy when multiple parties share the same email?

Use a web-based intra-OpenACS community email package to convey email subject/content, and send a generic notification to the user's email address. I believe a package like that is in development.

Maybe the zz-email-not-unique code could be added to the intra-site-messaging package.

Sites that want to keep the constraint might be satisfied with by just creating a parameter somewhere that automatically sets username to email_address on registration etc.

At the risk of offending Malte even further, I might suggest that he follow his own advice and to be even more cautious when removing or changing fundamental aspects of the datamodel and code base that has served so many people so well for so many years.

I'm not opposed to well thought-out changes However willy-nilly changes that are made because of a one-off problem with a client, without public discussion of the problem and possible solutions, are a problem. And such changes should not be made unless the person proposing and making them are willing to spend the time to track down potential problems and to make the commitment to fix them afterwards.

But anyway, I will not bring up a TIP but use this in our own patch which we just don't commit (as this has proven the best way for other companies who have the need for changes and don't have the luxury of lengthy debates). Which should solve the process problem from our side as, if we accidently make a commit which you feel should be discussed, we immediately remove it and put it into a patch. No big deal. If you feel this puts an unnecessary burden on the community (as obviously I disagree with your interpretation of the TIP rules and therefore am prone for running into this conflict again and again), feel free to ask us not to commit anything to core anymore, then we at least know where we stand.

As you mentioned the issue with application data links, feel free to start a discussion on that and we will make our point why we did it this way (as we did earlier, but I don't think that a list of IRC logs and forums posting links would change your perception). If you don't like then we will just put it into a seperate package as it would be totally unfair if we put this into a patch as nearly all packages we have been working on rely on that API [yeah, I know, very *evil* and sneaky behaviour of making a core change and making sure that removing it will render 5 packages useless 😊].