Create database user or store as table record?

Recently, someone posted a database user question on the Dream In Code forums. The gist of the question was, if you had to maintain a number of users for a web application, and each user needs an ID to log in, what’s the preferred method of doing it?

2 options were given. The first was to create an individual database ID for each user. The second was that all users use the same user ID to log in.

What I had in mind was, the database connection string. If individual database IDs were created, then when each user log in, unique connection strings are used to connect to the database. From what I know, this doesn’t scale very well, since the database throttles at a certain number of connections.

So the individual database ID case was no good. Having all users using the same ID would work. But there’s no personalisation, or you need some work to separate each user. Unless personalisation wasn’t an issue. Then there wouldn’t be any need to log in. I was a bit perplexed with the second option…

So I suggested that user IDs be stored in a database table. A database user ID is created specifically for online user log in’s. Every user has a unique ID to log in, and when logging in, the special database ID is used to form the connection string. Then the ID-password pair is used to verify against a table storing the user IDs.

Passwords would be hashed and stored in the database table. When a user logs in, the given password is hashed and compared with the one stored in the table.

This way, there’s only one unique connection string, and if I understand it correctly, it can be cached and reused, for better responsiveness.

I’ve dealt with both cases. In my work, there’s a department who handles the database. My applications are open only to internal users, and someone decided that user IDs must be controlled at the database side. And so each user has their own database ID. When logging in, the authentication process is handled by the database.

Basically I pass in a user ID and password. If a connection goes through, the user ID and password are valid.

This is sometimes infuriating, because my users are a fluid group. Some existing staff leave, or some new staff joins, and I have to ask the database department to update the list of IDs. The infuriating part is when a user asks me how to use the application and after some questioning, discover that she’s a new staff. Help desk support sometimes require me to question some of my assumptions…

The thing is, even though that department is handling the user IDs, I still have to keep a list of the IDs in a database table. The reason? User access. Certain users have rights to certain pages. The only way to control that is at the application level, not the database level.

On the other hand, I also maintain web applications where the user IDs are held in a database table. Maintaining the user list is as simple as an insert or a delete statement. There’s really only one database connection ID. This allowed me to easily scale the applications in terms of users.

I imagine the huge web sites such as the social media sites store users in a database table. I think creating thousands of database IDs is inefficient. Maybe I’m wrong. What do you think?

  1. Ben Barden

    I totally agree with you. I have a related question. Well, sort of. I’ve heard that we should never delete, only flag as deleted and hide from view. How do you feel about that?

  2. Vincent Tan

    It depends on the use of the table. If the table tends to get big, and needs to be accessed frequently, then it’s better to really delete the record. You can store the original record in another table (a log table so to speak) before deleting from the original table.

    This keeps the table down to a minimum size, and probably simplifies your SQL query. I’ve had to come up with complex nested select statements because I needed to get only the latest record, but there were multiple records of the same ID (in my case).

    On the other hand, if the current record and historical records need to be displayed together, and it’s worse if they were stored in separate tables, then the “flag as deleted” works fine.

    I propose the “really delete” approach whenever possible. Query speed and simplification are usually top priority for me.

    Do you have a specific case or use to share?

Comments are closed.