ERD 신민수 | Notion

erDiagram

Companies {
	INT id PK "UNIQUE, NOT NULL"
	STRING companyName "UNIQUE, NOT NULL"
	STRING companyCode "UNIQUE, NOT NULL"
	DateTime createdAt "NOT NULL" 
  DateTime updatedAt "NOT NULL"
}

Users {
	INT id PK "UNIQUE, NOT NULL"
	INT companyId FK "NOT NULL"
	STRING name "NOT NULL"
	STRING email "UNIQUE, NOT NULL"
	STRING password "NOT NULL"
	STRING employeeNumber "UNIQUE, NOT NULL"
	STRING phoneNumber "NOT NULL"
	STRING imageUrl "nullable"
	BOOLEAN isAdmin "NOT NULL, DEFAULT FALSE"
	DateTime createdAt "NOT NULL" 
  DateTime updatedAt "NOT NULL"
}

Customers {
	INT id PK "UNIQUE, NOT NULL"
	INT companyId FK "NOT NULL"
	STRING name "NOT NULL" 
	ENUM gender "NOT NULL"
	STRING phoneNumber "NOT NULL"
	ENUM ageGroup "nullable"
	ENUM region "nullable"
	STRING email "NOT NULL"
	STRING memo "nullable"
	INT contractCount "NOT NULL, DEFAULT 0"
	DateTime createdAt "NOT NULL" 
  DateTime updatedAt "NOT NULL"
}

Cars {
	INT id PK "UNIQUE, NOT NULL"
	INT companyId FK "NOT NULL"
	INT carModelId FK "NOT NULL"
	INT manufacturerId FK "NOT NULL"
	STRING carNumber "UNIQUE, NOT NULL"
	INT manufacturingYear "NOT NULL"
	INT mileage "NOT NULL"
	**INT** price "NOT NULL"
	INT accidentCount "NOT NULL"
	STRING explanation "nullable"
	STRING accidentDetails "nullable"
	ENUM carStatus "NOT NULL, DEFAULT possession"
	DateTime createdAt "NOT NULL" 
  DateTime updatedAt "NOT NULL"
}

Manufacturers {
	INT id PK "UNIQUE, NOT NULL"
	INT carModelId FK "NOT NULL"
	STRING name "NOT NULL"
	DateTime createdAt "NOT NULL" 
  DateTime updatedAt "NOT NULL"
}

CarModels {
	INT id PK "UNIQUE, NOT NULL"
	INT manufacturerId FK "NOT NULL"
	STRING name "NOT NULL"
  STRING type "NOT NULL"
	DateTime createdAt "NOT NULL" 
  DateTime updatedAt "NOT NULL"
}

Contracts {
	INT id PK "UNIQUE, NOT NULL"
	INT userId FK "NOT NULL"
	INT carId FK "NOT NULL"
	INT customerId FK "NOT NULL"
	INT companyId FK "NOT NULL"
	ENUM contractStatus "NOT NULL, DEFAULT carInspection"
	DateTime resolutionDate "NOT NULL"
	INT contractPrice "NOT NULL"
	DateTime createdAt "NOT NULL" 
  DateTime updatedAt "NOT NULL"
}

Meetings {
	INT id PK "UNIQUE, NOT NULL"
	INT contractId FK "NOT NULL"
	DateTime date "NOT NULL"
	DateTime createdAt "NOT NULL" 
  DateTime updatedAt "NOT NULL"
}

Alarms {
  INT id PK "UNIQUE, NOT NULL"
  INT meetingId FK "NOT NULL"
  DateTime time "NOT NULL"
  DateTime createdAt "NOT NULL" 
  DateTime updatedAt "NOT NULL"
}

ContractDocuments {
	INT id PK "UNIQUE, NOT NULL"
	INT constractId FK "NOT NULL"
	INT companyId FK "NOT NULL"
	STRING fileName "NOT NULL"
	STRING filePath "NOT NULL"
	INT fileSize "NOT NULL"
	DateTime createdAt "NOT NULL" 
  DateTime updatedAt "NOT NULL"
}

Cars ||--o| Contracts :"has Contract"
Users ||--o{ Contracts:"manages"
Contracts ||--o{ ContractDocuments:"has"
Customers ||--o{ Contracts:"make a contract"
Companies ||--o{ Users :"employs"
Companies ||--o{ Cars :"ownedBy"
Companies ||--o{ Customers :"serves"
Companies ||--o{ Contracts :"hold"
Contracts ||--o{ Meetings : "has"
Meetings ||--o{ Alarms : "has"
Manufacturers ||--o{ CarModels : "belong to"
CarModels ||--o{ Cars : "belong to"

schema.prisma

model Company {
  id           Int       @id @default(autoincrement())
  companyName  String    @unique
  companyCode  String    @unique
  userCount    Int
  authCode     String
  createdAt    DateTime  @default(now())
  updatedAt    DateTime  @updatedAt

  users        User[]
  customers    Customer[]
  cars         Car[]
  contracts    Contract[]
  contractDocuments ContractDocument[]
}

model User {
  id             Int       @id @default(autoincrement())
  companyId      Int
  name           String
  email          String    @unique
  password       String
  employeeNumber String    @unique
  phoneNumber    String
  imageUrl       String?   
  isAdmin        Boolean   @default(false)
  createdAt      DateTime  @default(now())
  updatedAt      DateTime  @updatedAt

  company        Company   @relation(fields: [companyId], references: [id])
  contracts      Contract[]
}

model Customer {
  id             Int       @id @default(autoincrement())
  companyId      Int
  name           String
  gender         Gender
  phoneNumber    String
  ageGroup       AgeGroup?
  region         Region?
  email          String?
  memo           String?
  contractCount  Int       @default(0)
  createdAt      DateTime  @default(now())
  updatedAt      DateTime  @updatedAt

  company        Company   @relation(fields: [companyId], references: [id])
  contracts      Contract[]
}

model Car {
  id              Int       @id @default(autoincrement())
  companyId       Int
  carNumber       String    @unique
  manufacturer    Manufacturer
  model           CarModel
  type            CarType
  manufacturingYear Int
  mileage         Int
  price           Int
  accidentCount   Int
  explanation     String?
  accidentDetails String?
  carStatus       CarStatus @default(possession)
  createdAt       DateTime  @default(now())
  updatedAt       DateTime  @updatedAt

  company         Company   @relation(fields: [companyId], references: [id])
  contracts       Contract[]
}

model Contract {
  id              Int       @id @default(autoincrement())
  userId          Int
  carId           Int
  customerId      Int
  companyId       Int
  contractStatus  ContractStatus @default(carInspection)
  resolutionDate  DateTime
  contractPrice   Int
  createdAt       DateTime  @default(now())
  updatedAt       DateTime  @updatedAt

  user            User      @relation(fields: [userId], references: [id])
  car             Car       @relation(fields: [carId], references: [id])
  customer        Customer  @relation(fields: [customerId], references: [id])
  company         Company   @relation(fields: [companyId], references: [id])
  meeting         Meeting?
  contractDocuments ContractDocument[]
}

model Meeting {
  id          Int       @id @default(autoincrement())
  contractId  Int
  date        DateTime
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  contract    Contract  @relation(fields: [contractId], references: [id])
  alarm       Alarm?
}

model Alarm {
  id          Int       @id @default(autoincrement())
  meetingId   Int
  time        DateTime
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  meeting     Meeting   @relation(fields: [meetingId], references: [id])
}

model ContractDocument {
  id          Int       @id @default(autoincrement())
  contractId  Int      
  companyId   Int
  fileName    String
  filePath    String
  fileSize    Int
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  contract    Contract  @relation(fields: [contractId], references: [id])
  company     Company   @relation(fields: [companyId], references: [id])
}

model CarCategory {
  id            Int          @id @default(autoincrement())
  modelName     String                           
  manufacturer  String                          
  type          String                             
  createdAt     DateTime     @default(now())
  updatedAt     DateTime     @updatedAt

  cars          Car[]        

enum Gender {
  male
  female
}

enum AgeGroup {
  AGE_10
  AGE_20
  AGE_30
  AGE_40
  AGE_50
  AGE_60
  AGE_70
  AGE_80
  AGE_90
}

enum Region {
  SEOUL
  BUSAN
  DAEGU
  INCHEON
  GWANGJU
  DAEJEON
  ULSAN
  SEJONG
  GYEONGGI
  GANGWON
  CHUNGBUK
  CHUNGNAM
  JEONBUK
  JEONNAM
  GYEONGBUK
  GYEONGNAM
  JEJU
}

enum Manufacturer {
  HYUNDAI
  KIA
  GENESIS
  SSANGYONG
  TOYOTA
  BMW
  TESLA
}

enum HyundaiModel {
  SONATA
  AVANTE
  GRANDEUR
  TUCSON
  SANTA_FE
  IONIQ_5
  IONIQ_6
}

enum KiaModel {
  K3
  K5
  K8
  SPORTAGE
  SORENTO
  CARNIVAL
  EV6
}

enum GenesisModel {
  G70
  G80
  G90
  GV70
  GV80
}

enum TeslaModel {
  MODEL_S
  MODEL_3
  MODEL_X
  MODEL_Y
  CYBERTRUCK
}

enum BmwModel {
  SERIES_3
  SERIES_5
  SERIES_7
  X3
  X5
  I4
  I7
}

enum SsangyongModel {
  TIVOLI
  KORANDO
  REXTON
  REXTON_SPORTS
  MUSSO
  CHAIRMAN
}

enum ToyotaModel {
  CAMRY
  COROLLA
  PRIUS
  RAV4
  HIGHLANDER
  LAND_CRUISER
  SIENNA
  MIRAI
  SUPRA
  TACOMA
}

enum CarType {
  COMPACT       
  SUBCOMPACT    
  MID_SIZE      
  FULL_SIZE      
  SUV          
  EV            
  ETC        
}

enum CarStatus {
  POSSESSION              
  CONTRACT_PROCEEDING     
  CONTRACT_COMPLETED      
}

enum ContractStatus {
  CAR_INSPECTION          
  PRICE_NEGOTIATION     
  CONTRACT_DRAFT         
  CONTRACT_SUCCESSFUL    
  CONTRACT_FAILED         
}

enum 정리

enum Gender {
	male
	female
}

enum AgeGroup {
	10
	20
	30
	40
	50
	60
	70
	80
	90
}

enum Region {
  SEOUL = '서울특별시',
  BUSAN = '부산광역시',
  DAEGU = '대구광역시',
  INCHEON = '인천광역시',
  GWANGJU = '광주광역시',
  DAEJEON = '대전광역시',
  ULSAN = '울산광역시',
  SEJONG = '세종특별자치시',
  GYEONGGI = '경기도',
  GANGWON = '강원도',
  CHUNGBUK = '충청북도',
  CHUNGNAM = '충청남도',
  JEONBUK = '전라북도',
  JEONNAM = '전라남도',
  GYEONGBUK = '경상북도',
  GYEONGNAM = '경상남도',
  JEJU = '제주특별자치도'
}

enum Manufacturer {
  HYUNDAI = '현대',
  KIA = '기아',
  GENESIS = '제네시스',
  SSANGYONG = '쌍용',
  TOYOTA = '토요타',
  BMW = 'BMW',
  TESLA = '테슬라'
 }

enum HyundaiModel {
  SONATA = '쏘나타',
  AVANTE = '아반떼',
  GRANDEUR = '그랜저',
  TUCSON = '투싼',
  SANTA_FE = '싼타페',
  IONIQ_5 = '아이오닉 5',
  IONIQ_6 = '아이오닉 6'
}

enum KiaModel {
  K3 = 'K3',
  K5 = 'K5',
  K8 = 'K8',
  SPORTAGE = '스포티지',
  SORENTO = '쏘렌토',
  CARNIVAL = '카니발',
  EV6 = 'EV6',
}

enum GenesisModel {
  G70 = 'G70',
  G80 = 'G80',
  G90 = 'G90',
  GV70 = 'GV70',
  GV80 = 'GV80'
}

enum TeslaModel {
  MODEL_S = 'Model S',
  MODEL_3 = 'Model 3',
  MODEL_X = 'Model X',
  MODEL_Y = 'Model Y',
  CYBERTRUCK = 'Cybertruck'
}

enum BmwModel {
  SERIES_3 = '3시리즈',
  SERIES_5 = '5시리즈',
  SERIES_7 = '7시리즈',
  X3 = 'X3',
  X5 = 'X5',
  I4 = 'i4',
  I7 = 'i7'
}

enum SsangyongModel {
  TIVOLI = '티볼리',
  KORANDO = '코란도',
  REXTON = '렉스턴',
  REXTON_SPORTS = '렉스턴 스포츠',
  MUSSO = '무쏘',
  CHAIRMAN = '체어맨'
}

enum ToyotaModel {
  CAMRY = '캠리',
  COROLLA = '코롤라',
  PRIUS = '프리우스',
  RAV4 = 'RAV4',
  HIGHLANDER = '하이랜더',
  LAND_CRUISER = '랜드크루저',
  SIENNA = '시에나',
  MIRAI = '미라이',
  SUPRA = '수프라',
  TACOMA = '타코마'
}

enum type {
  COMPACT = '경차',        
  SUBCOMPACT = '준중형',    
  MID_SIZE = '중형',        
  FULL_SIZE = '대형',       
  SUV = 'SUV',             
  EV = '전기차',
  ETC = '기타'          
}

enum carStatus { 
  POSSESSION = '보유중',       
  CONTRACT_PROCEEDING = '계약 진행중',                 
  CONTRACT_COMPLETED = '계약 완료',                    
}

enum contractStatus { 
  CAR_INSPECTION = '차량 조회중',       
  PRICE_NEGOTIATION = '가격 협상중',                 
  CONTRACT_DRAFT = '계약서 작성중',              
  CONTRACT_SUCCESSFUL = '계약 성공',              
  CONTRACT_FAILED = '계약 실패'       
}