Database design

## Table ### Naming Rule #### Use lower case + snake case users // OK user_categories // OK USERS // NG Users // NG userCategories // NG #### Use the plural users // OK user // NG #### n:n table ex. related users and images users_images // OK ## Column ### Required Columns id // Primary key(auto increment) created_at updated_at ### Naming Rule #### Use the noun title // OK description // OK #### Others table id ex. id on users table user_id // OK users_id // NG #### datetime posted_at // OK post_time // NG #### boolean published // OK publish_flag // NG ## Index ### Naming Rule #### Index `idx_<table>_XX` idx_users_01 // OK #### Unique Index`ui_<table>_XX` ui_users_01 // OK ## Others ### DO NOT logical deletion deleted_at // NG deleted // NG ### DO NOT create composite primary keys Use `id` for primary key ### DO NOT use foreign key basically ## MySQL ### type Use `bigint` for number Use `varcher` for string Use `datetime` for date Use `tinyint` for boolean ### autoupdate for created_at, updated_at #### `created_at` ``` `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ``` #### `updated_at` ``` `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ```