Skip to content

Instantly share code, notes, and snippets.

@kijtra kijtra/user.sql
Last active Oct 28, 2016

Embed
What would you like to do?
User information store DB(MySQL) structure example
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;
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;
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;
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;
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;
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.