Improving My Database Schema

[Xtremer360]

Here is my database schema for my user information. I'm curious to know what everyone thinks as far as better ways to store my information meaning if its grouped together correctly in the right tables.

 

Table Name: users

Fields: id(int(11)),

        user_id(mediumint(5)),

        username(varchar(50)),

        password(varchar(255)),

        password2(varchar(255)),

        email(varchar(255)),

        first_name(varchar(255)),

        last_name(varchar(255)),

        users_roles_id(tinyint(1)),

        users_statuses_id(tinyint(1))

 

Table: users_logins

Fields: id(tinyint(4)),

        user_id(mediumint(5)),

        number_of_logins(tinyint(4)),

        failed_logins (tinyint(1)),

        ip_address(varchar(50)),

        lock_date (datetime)

 

Table: users_logins_sessions

Fields: id (tinyint(4)), 

        user_id (mediumint(5)),

        session_id (varchar(40)),

        ip_address (varchar(16)),

        user_agent (archar(150)),

        session_started (datetime)

[scootstah]

What is password2?

 

And how are you storing the passwords? If you are hashing it like you should be, it will be a consistent length and so you can use char instead of varchar.

[thehippy]

SQL has naming conventions and style standards, and conventions dictate that table names be singular of the entity that they store attributes of.  A single entity of user data would have that table name be called user and and attribute in the user table be accessed as user.name and so forth.  Also it makes your code look cleaner and that's always nice.

 

Take another coder's perspective into account with regards to your naming, if they're looking at it for the first time.  For example password really isn't descriptive enough password_md5 and password_salt or password_sha1 would be better and wouldn't have the coder looking through code to make proper use of the data.

 

Do your integer values need to be signed (positive/negative)?

 

Try not to use SQL extensions when you don't have to, such as MEDIUMINT, TINYINT, where INT(5) and INT(1) provides the same functionality.  If you ever switch RDMS it can be a headache.

 

users.id and users.user_id is confusing, you should clarify it.

 

tinyint(1) is a bit/boolean field, users_logins.failed_logins, users,_users_roles_id, users_statuses_id doesn't make sense to me, unless there are only two of each.

 

users.users_roles_id and users.users_statuses_id, this seems limiting, a user can only have one role and one status.

 

If I understand what you're trying to do with the users_logins table, I'm unsure that it provides that purpose.  Do failed login attempts really belong to the user they were attempting to login as?  A more generalized login attempt logging mechanism may be more robust.

 

Standardize your 'id' fields across tables by using the same sized column data type, makes life easier.

 

The User-Agent header has no limit specified in any of the RFCs, 150 characters will not contain many you'll find  in the wild, 255 isn't enough, 500 will catch nearly all but the odd exception.

 

I know its not strict convention but I like to have 'FK' in the name of foriegn key columns.  So users_logins_sessions.user_id becomes users_logins_sessions.user_id_fk.

 

You didn't mention the table types or indexes you would be using so I didn't assume or comment. I'm too tired, sorry if I didn't make much sense.  I'll check back after I've had some sleep.

[scootstah]

The User-Agent header has no limit specified in any of the RFCs, 150 characters will not contain many you'll find  in the wild, 255 isn't enough, 500 will catch nearly all but the odd exception.

 

I usually truncate the user agent into 100 or 150 characters. I don't think it is that pertinent to store the entire thing.