E-Mail is not unique. There are families who use the same email, there are employees who use the same email, there are villages who use the same e-mail. Therefore having a unique constraint on e-mails while the open world actually prooves that more parties (organizations and persons) use the same e-amil is a bad idea. If you force users (note the difference between person and user) to use their e-mail address for login, obviously the unique constraint on e-mail makes sense. I'd even go so far and agree that the email of a user (person which has a password to login to the community) in the system should be unique, to avoid the spam problem, though I still wouldn't enforce it (why do we have the username).
Oh, and while the box of pandora is already open who says that users in the community system have to have an e-mail? If I want to participate in the forums and make assessments and have my dotlrn portal, I do not need an e-mail address. I need a login, and the email makes it easier to communicate with myself, but why should I be forced to provide the email or, for that matter, why should any user of OpenACS force his users to provide e-mails (this refers to "not null" constraints).
Another unique constraint which we had been banging our head against quite often in the last months is the unique constraint on cr_item name with parent_id, though I still agree this is a good idea.
Here is the szenario though:
- Original file is uploaded
- User uploads a first translation with the same filename (e.g french)
- User two uploads a second translation with the same filename (e.g. spanish)
As we do not have a unique constraint on cr_item, locale, parent_id we are forced to either
- Use separate folders for the files (of each languague)
- Rename the name of the file
There have been some other issues (multiple revisions of a file where you need to know which revision was following what earlier revision, resulting in us not using revisions in the first place but different folders).
Note: I'm not accusing anyone of making a wrong decision (even if I say that it has been a bad idea), I just note that realities of day to day development driven by client needs has shown that some assumptions should better not have been made.
So my warning / plea is solemnly: If you make a unique or not null constraint in the datamodell, make sure that your assumption is correct and can be remodelled in the real world, as changes in the datamodell and application once you made this assumption, are painful, result in discussions like this and most likely wont be made in the first place or not committed back, resulting in forks all over the place.
