Return to Answer

Users table

'username' VARCHAR(25) NOT NULL,

'password' VARCHAR(255) NOT NULL,

'first_name' VARCHAR(255) NOT NULL,
'last_name' VARCHAR(255) NOT NULL,

Additionally, do you really need the real name of the user? Doesn't the username suffices? Or the real name is to be displayed somewhere? All in all, I would put it as

'gender' ENUM('M', 'F'),

'city' VARCHAR(255),
'state' VARCHAR(255),
'country' VARCHAR(255),

'birth_date' VARCHAR(255),

'active' ENUM('yes','no') NOT NULL DEFAULT 'no',

An enum is not the most optimal data type here. It's better to use BIT instead.

Table Posts

Generally, the layout looks good, I would not split it into images/videos, at least for now. This is due both storing basically the very same data, with only the file type differing. Only consider such a split if each type requires different columns. Other than that, I have this few tips about this table:

'post_url' VARCHAR(255) NOT NULL,

Table Followings

PRIMARY KEY ('user_id', 'following_id'),
UNIQUE INDEX ('following_id', 'user_id')

General comments

One thing I would do is to be explicit about nullability of each column, even if the default favors your particular case. Being explicit doesn't hurts, and it better helps readability, so I would suggest always stating each column as NULL/NOT NULL.

You might consider the posibility of allowing users like comments too, in addition to the main posts (as the likes table handles).

You can also consider the option of adding some history tables for registering each edition of posts (pretty much like StackOverflow does), in addition to the date_updated column, if you may need additional audits or rollback capability.

Users table

'username' VARCHAR(25) NOT NULL,

'password' VARCHAR(255) NOT NULL,

'first_name' VARCHAR(255) NOT NULL, 
'last_name' VARCHAR(255) NOT NULL,

Additionally, do you really need the real name of the user? Doesn't the username suffice? Or is the real name to be displayed somewhere? All in all, I would put it as

'gender' ENUM('M', 'F'),

'city' VARCHAR(255), 
'state' VARCHAR(255), 
'country' VARCHAR(255),

'birth_date' VARCHAR(255),

'active' ENUM('yes','no') NOT NULL DEFAULT 'no',

An enum is not the most optimal data type here. It's better to use BIT instead.

Posts Table

Generally, the layout looks good, I would not split it into images/videos, at least for now. This is because both of them store basically the very same data, with only the file type differing. Only consider such a split if each type requires different columns. Other than that, I have these few tips about this table:

'post_url' VARCHAR(255) NOT NULL,

Followings Table

PRIMARY KEY ('user_id', 'following_id'), 
UNIQUE INDEX ('following_id', 'user_id')

General comments

One thing I would do is to be explicit about nullability of each column, even if the default favors your particular case. Being explicit doesn't hurt, and it really helps readability, so I would suggest always stating each column as NULL/NOT NULL.

You might consider the possibility of allowing users to like comments too, in addition to the main posts (as the likes table handles).

You can also consider the option of adding some history tables for registering each edit of posts (pretty much like Stack Overflow does), in addition to the date_updated column, if you may need additional audits or rollback capability.

An enum is not the most optimal data type here. It's better to use BIT instead.

An enum is not the most optimal data type here. It's better to use BIT instead.

This smells at first sight. Maybe I'm wrong, but this sounds as a plain-text password, which is utterly wrong. Column size should match the output size of the used hash function, plus the size of the salt and hash parameters (or split those into separate columns). And don't even think of SHA1 or MD5 :P Use PBKDF2, bcrypt or scrypt for this. Look here for a more detailed discussion of password hashing.

This smells at first sight. Maybe I'm wrong, but this sounds as a plain-text password, which is utterly wrong. Column size should match the output size of the used hash function, plus the size of the salt and hash parameters (or split those into separate columns). And don't even think of SHA1 or MD5 :P Use PBKDF2, bcrypt or scrypt for this. Look here for a more detailed discussion of password hashing.