コンテンツにスキップ

m_employee

概要

従業員マスタ

テーブル定義 
CREATE TABLE `m_employee` (
  `emp_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '従業員ID',
  `company_id` bigint unsigned NOT NULL COMMENT '会社ID',
  `org_id` bigint unsigned NOT NULL COMMENT '所属組織ID',
  `position_id` bigint unsigned DEFAULT NULL COMMENT '役職ID',
  `emp_code` varchar(20) COLLATE utf8mb4_ja_0900_as_cs_ks NOT NULL COMMENT '社員番号',
  `azure_ad_object_id` char(36) COLLATE utf8mb4_ja_0900_as_cs_ks DEFAULT NULL COMMENT 'Azure AD Object ID',
  `last_name` varchar(50) COLLATE utf8mb4_ja_0900_as_cs_ks NOT NULL COMMENT '姓',
  `first_name` varchar(50) COLLATE utf8mb4_ja_0900_as_cs_ks NOT NULL COMMENT '名',
  `last_name_kana` varchar(50) COLLATE utf8mb4_ja_0900_as_cs_ks DEFAULT NULL COMMENT '姓（カナ）',
  `first_name_kana` varchar(50) COLLATE utf8mb4_ja_0900_as_cs_ks DEFAULT NULL COMMENT '名（カナ）',
  `employment_type` enum('REGULAR','CONTRACT','PARTTIME','TEMP','DISPATCH') COLLATE utf8mb4_ja_0900_as_cs_ks NOT NULL DEFAULT 'REGULAR' COMMENT '雇用区分',
  `status` enum('ACTIVE','LEAVE','INACTIVE','RETIRED') COLLATE utf8mb4_ja_0900_as_cs_ks NOT NULL DEFAULT 'ACTIVE' COMMENT '在籍ステータス',
  `email` varchar(150) COLLATE utf8mb4_ja_0900_as_cs_ks NOT NULL COMMENT '社用メールアドレス',
  `phone_number` varchar(20) COLLATE utf8mb4_ja_0900_as_cs_ks DEFAULT NULL COMMENT '連絡先電話番号',
  `valid_from` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '有効開始日時',
  `valid_to` datetime NOT NULL DEFAULT '9999-12-31 23:59:59' COMMENT '有効終了日時',
  `deleted_at` datetime DEFAULT NULL COMMENT '論理削除日時',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '登録日時',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新日時',
  `version` int unsigned NOT NULL DEFAULT '1' COMMENT 'バージョン',
  PRIMARY KEY (`emp_id`),
  UNIQUE KEY `company_id` (`company_id`,`emp_code`,`deleted_at`),
  KEY `org_id` (`org_id`),
  KEY `position_id` (`position_id`),
  CONSTRAINT `m_employee_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `m_company` (`company_id`),
  CONSTRAINT `m_employee_ibfk_2` FOREIGN KEY (`org_id`) REFERENCES `m_organization` (`org_id`),
  CONSTRAINT `m_employee_ibfk_3` FOREIGN KEY (`position_id`) REFERENCES `m_position` (`position_id`)
) ENGINE=InnoDB AUTO_INCREMENT=[Redacted by tbls] DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs_ks COMMENT='従業員マスタ'

カラム一覧

# 名前 タイプ デフォルト値 Nullable Extra Definition 子テーブル 親テーブル コメント
1 emp_id bigint unsigned false auto_increment m_employee_account m_employee_delegate m_employee_preference 従業員ID
2 company_id bigint unsigned false m_company 会社ID
3 org_id bigint unsigned false m_organization 所属組織ID
4 position_id bigint unsigned true m_position 役職ID
5 emp_code varchar(20) false 社員番号
6 azure_ad_object_id char(36) true Azure AD Object ID
7 last_name varchar(50) false 姓
8 first_name varchar(50) false 名
9 last_name_kana varchar(50) true 姓（カナ）
10 first_name_kana varchar(50) true 名（カナ）
11 employment_type enum('REGULAR','CONTRACT','PARTTIME','TEMP','DISPATCH') REGULAR false 雇用区分
12 status enum('ACTIVE','LEAVE','INACTIVE','RETIRED') ACTIVE false 在籍ステータス
13 email varchar(150) false 社用メールアドレス
14 phone_number varchar(20) true 連絡先電話番号
15 valid_from datetime CURRENT_TIMESTAMP false DEFAULT_GENERATED 有効開始日時
16 valid_to datetime 9999-12-31 23:59:59 false 有効終了日時
17 deleted_at datetime true 論理削除日時
18 created_at datetime CURRENT_TIMESTAMP false DEFAULT_GENERATED 登録日時
19 updated_at datetime CURRENT_TIMESTAMP false DEFAULT_GENERATED on update CURRENT_TIMESTAMP 更新日時
20 version int unsigned 1 false バージョン

制約一覧

# 名前 タイプ 定義
1 company_id UNIQUE UNIQUE KEY company_id (company_id, emp_code, deleted_at)
2 m_employee_ibfk_1 FOREIGN KEY FOREIGN KEY (company_id) REFERENCES m_company (company_id)
3 m_employee_ibfk_2 FOREIGN KEY FOREIGN KEY (org_id) REFERENCES m_organization (org_id)
4 m_employee_ibfk_3 FOREIGN KEY FOREIGN KEY (position_id) REFERENCES m_position (position_id)
5 PRIMARY PRIMARY KEY PRIMARY KEY (emp_id)

INDEX一覧

# 名前 定義
1 org_id KEY org_id (org_id) USING BTREE
2 position_id KEY position_id (position_id) USING BTREE
3 PRIMARY PRIMARY KEY (emp_id) USING BTREE
4 company_id UNIQUE KEY company_id (company_id, emp_code, deleted_at) USING BTREE

ER図

er

Generated by tbls