fork download 
  1. -- users
  2. CREATE TABLE users (
  3.   id INTEGER PRIMARY KEY AUTOINCREMENT,
  4.   email TEXT UNIQUE NOT NULL,
  5.   password_hash TEXT NOT NULL,
  6.   full_name TEXT,
  7.   role TEXT DEFAULT 'student', -- student | teacher | admin
  8.   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  9.   updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  10. );
  11.  
  12. -- subjects (môn học /categories)
  13. CREATE TABLE subjects (
  14.   id INTEGER PRIMARY KEY AUTOINCREMENT,
  15.   name TEXT NOT NULL,
  16.   color TEXT,
  17.   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  18. );
  19.  
  20. -- tasks: công việc học tập / bài tập
  21. CREATE TABLE tasks (
  22.   id INTEGER PRIMARY KEY AUTOINCREMENT,
  23.   title TEXT NOT NULL,
  24.   description TEXT,
  25.   subject_id INTEGER,
  26.   owner_id INTEGER NOT NULL, -- người tạo
  27.   assignee_id INTEGER,       -- người được giao (có thể null)
  28.   due_date TIMESTAMP,
  29.   priority INTEGER DEFAULT 2, -- 1 low,2 medium,3 high
  30.   status TEXT DEFAULT 'todo', -- todo | in_progress | done | archived
  31.   reminder TIMESTAMP,         -- thời gian nhắc nhở
  32.   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  33.   updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  34.   FOREIGN KEY(subject_id) REFERENCES subjects(id),
  35.   FOREIGN KEY(owner_id) REFERENCES users(id),
  36.   FOREIGN KEY(assignee_id) REFERENCES users(id)
  37. );
  38.  
  39. -- attachments (tài liệu đính kèm)
  40. CREATE TABLE attachments (
  41.   id INTEGER PRIMARY KEY AUTOINCREMENT,
  42.   task_id INTEGER NOT NULL,
  43.   filename TEXT NOT NULL,
  44.   file_path TEXT NOT NULL,
  45.   uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  46.   FOREIGN KEY(task_id) REFERENCES tasks(id)
  47. );
  48.  
  49. -- grades / đánh giá (tuỳ chọn)
  50. CREATE TABLE grades (
  51.   id INTEGER PRIMARY KEY AUTOINCREMENT,
  52.   task_id INTEGER NOT NULL,
  53.   student_id INTEGER NOT NULL,
  54.   score REAL,
  55.   feedback TEXT,
  56.   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  57.   FOREIGN KEY(task_id) REFERENCES tasks(id),
  58.   FOREIGN KEY(student_id) REFERENCES users(id)
  59. );
Success #stdin #stdout 0.01s 5320KB
comments (?)
stdin 
Standard input is empty
stdout 
Standard output is empty
https://ideone.com/ZtNMxC
language:
SQLite (sqlite 3.27.2)
created:
18 days ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!