Do i need a special database constraint on this table?

Question

Hi folks,

i've got a commenting system in our database. Just like stackoverflow -> each post has a list of comments. kewl.

Anonymous people can add a comment or registered users also.

In my table, i'm thinking of having the following:

• Userid int NULLABLE
• AnonymousNickname varchar(100) NULLABLE
• AnonymousEmail varchar(200) NULLABLE

now it can only be one or the other. You're either registered or your not.

So should i enforce some type of constraint that says one or the other .. and if so, how?

NOTE: Database is Microsoft Sql Server 2008.

cheers!

Answer 1

A simple constraint would be

(Userid is not null and AnonymousNickname is null and AnonymousEmail is null)
or (Userid is null and AnonymousNickname is not null and AnonymousEmail is not null)

This will force only one or the other to be set. It sounds like this would be reasonable based on your application. It's up to you to decide to enforce it in the database or in your application. If it's a hard constraint that other parts of your application depend on, then I would probably enforce it in the database and detect it through validation in your code.

Answer 2

I wouldn't include the constraint. You should rename the anonymous name field to name. When a registered user leaves a comment, put their username into the name field. This saves you a lookup against the user table when you list out your comments. Also, if a user is deleted from the user table, their comments outlive them.