User information store DB(MySQL) structure example

user.sql

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL DEFAULT '',
  `email` VARCHAR(100) NOT NULL DEFAULT '',
  `password` CHAR(60) NOT NULL DEFAULT '', /* Use PHP's password_hash() */
  `display_name` VARCHAR(191) NOT NULL DEFAULT '',
  `url` VARCHAR(191) NOT NULL DEFAULT '',
  `registered_at` DATETIME NULL DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX (`name`),
  UNIQUE INDEX (`email`)
) ENGINE=InnoDB;

user_login.sql

DROP TABLE IF EXISTS `user_login`;

CREATE TABLE `user_login` (
  `user_id` BIGINT(20) UNSIGNED NOT NULL,
  `ip` VARCHAR(15) NOT NULL DEFAULT '',
  `hostname` VARCHAR(191) NOT NULL DEFAULT '',
  `useragent` VARCHAR(191) NOT NULL DEFAULT '',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX (`user_id`),
  FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

user_meta.sql

DROP TABLE IF EXISTS `user_meta`;

CREATE TABLE `user_meta` (
  `user_id` BIGINT(20) UNSIGNED NOT NULL,
  `meta_key` VARCHAR(50) NOT NULL DEFAULT '', /* e.g.) reset_email, reset_password, login_fail_count, ... */
  `meta_value` VARCHAR(191) NOT NULL DEFAULT '',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE INDEX (`user_id`, `meta_key`),
  INDEX (`user_id`),
  FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

user_oauth.sql

DROP TABLE IF EXISTS `user_oauth`;

CREATE TABLE `user_oauth` (
  `user_id` BIGINT(20) UNSIGNED NOT NULL,
  `provider` VARCHAR(20) NOT NULL DEFAULT '', /* e.g.) google, twitter, facebook, ... */
  `token` VARCHAR(191) NOT NULL DEFAULT '',
  `expire_at` DATETIME NULL DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX (`user_id`),
  UNIQUE INDEX (`token`),
  FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

user_role.sql

DROP TABLE IF EXISTS `user_role`;

CREATE TABLE `user_role` (
  `user_id` BIGINT(20) UNSIGNED NOT NULL,
  `role` VARCHAR(50) NOT NULL DEFAULT '', /* e.g.) admin, staff, banned, ... */
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE INDEX (`user_id`, `role`),
  INDEX (`user_id`),
  INDEX (`role`),
  FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

user_token.sql

DROP TABLE IF EXISTS `user_token`;

CREATE TABLE `user_token` (
  `user_id` BIGINT(20) UNSIGNED NOT NULL,
  `is_used` TINYINT(1) NOT NULL DEFAULT 0,
  `name` VARCHAR(50) NOT NULL DEFAULT '', /* e.g.) auto_login, email_reset, activation, ... */
  `token` VARCHAR(191) NOT NULL DEFAULT '',
  `expire_at` DATETIME NULL DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE INDEX (`user_id`, `name`),
  INDEX (`user_id`),
  INDEX (`is_used`),
  UNIQUE INDEX (`token`),
  FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;