IgnoreCase vs ToUpper/ToLower Database Query-CodePudding

I'm storing usernames in my database but wish to retain the username casing so that users can have usernames such as "FooBAR".
I also wish for usernames to be unique (regardless of case) - "BaR" would cause "bar" to be unavailable.

I see three ways of doing this:

Firstly, I could query my Users table like so:

bool usernameAvailable = !_context.Users.Any(u => String.Equals(u.Username, username, StringComparison.OrdinalIgnoreCase));

Secondly, I could convert both usernames to upper or lower like so:

username = username.ToUpper();
bool usernameAvailable = !_context.Users.Any(u => u.Username.ToUpper() == username);

Thirdly, I could create a second column in my table that stores the upper/lowercase version of the username:

username = username.ToUpper();
bool usernameAvailable = !_context.Users.Any(u => u.UsernameUpper == username);

This would require more space in the database but would be the easiest to query.

Any insight would be appreciated.

This has almost certainly been answered previously, so please point me in the direction of any previous posts as i'm unable to find what i'm looking for.

CodePudding user response：

Neither is a good option. This problem is a perfect match for a simple UNIQUE INDEX on the database side, together with a case-insensitive COLLATION on the username column. Thus, the client just needs to do a simple equality check and the DB will use the index to find if a matching name exists or not, including case-insensitive checks if you want.

Consider that you need such a check when you add a new user (to check for duplicates) and also when a user tries to login (so you check if the account exist). In both cases an index ensures the uniqueness needed for usernames and speed up the queries.

For the particular case of SQLite, an unique index that is case insensitive can be created like this:

CREATE TABLE users (
    UserName TEXT
    /*Other columns go here*/
    UNIQUE (UserName COLLATE NOCASE)
)

That'll prevent adding two usernames that only differ in case, in addition to speeding up retrieval. On the client side, just don't bother about casing. When users login, just do a normal comparison, and the DB will match them even if they just differ in casing. For the registration, do the INSERT right away, the DB will blow up and reject it if there is a casing difference, from where you can present the user a "username already taken" message.

Also look at this post (from where I took the basic idea): https://stackoverflow.com/a/20914992/2557263

CodePudding user response：

It depends on what your priority is, should your app be fast or should it use less database space, i think the time difference is negligable here, you should use the first method.

Here's an article on wikipedia about this dilemma https://en.wikipedia.org/wiki/Space–time_tradeoff