我正在尝试在c ++中使用sqlite3 create语句和外键,并提出了4个要求。
1) a employer can create a job position
2) a employer can view the form reject or shortlist the applicant for interview
3) a applicant may apply for a job position
4) a applicant must fill up their details and submit the form if they are keen to apply for a position
这是我写的
雇主表
/*SQL statement for employer table*/
char *sql;
sql = "CREATE TABLE EMPLOYER(" \
"ID INT PRIMARY KEY NOT NULL," \
"NAME TEXT NOT NULL," \
"ROLE TEXT NOT NULL );";
工作表
/*SQL statement for jobposition table */
char *sql;
sql = "CREATE TABLE JOBPOSITION(" \
"ID INT PRIMARY KEY NOT NULL," \
"JOBPOSITION TEXT NOT NULL," \
"OUTCOME TEXT NOT NULL );";
表格表
/*SQL statement for form table*/
char *sql;
sql = "CREATE TABLE FORM (" \
"ID INT PRIMARY KEY NOT NULL," \
"FOREIGN KEY (ID) REFERENCES APPLICANT(ID)",
"FOREIGN KEY (ID) REFERENCES JOBPOSITION(ID)",
FOREIGN KEY (ID) REFERENCES EMPLOYER(ID)" );";
申请人表
/*SQL statement for applicant table*/
char *sql;
sql = "CREATE TABLE APPLICANT (" \
"ID INT PRIMARY KEY NOT NULL," \
"APPLICANT_NAME TEXT NOT NULL," \
"APPLICANT_ADDRESS TEXT NOT NULL," );";
我真的不确定这是否正确,因为我刚开始学习数据库并需要一些帮助。
最佳答案
我不知道您所说的“正确”是什么意思,所以我将向您展示我将如何做:
CREATE TABLE users (
id INT NOT NULL PRIMARY_KEY AUTO_INCREMENT,
name TEXT NOT NULL,
address TEXT NOT NULL,
role TEXT NOT NULL, /* employer, applicant etc. */
);
CREATE TABLE job_positions (
id INT NOT NULL PRIMARY_KEY AUTO_INCREMENT,
creator_id INT NOT NULL FOREIGN_KEY REFERENCES users(id),
description TEXT NOT NULL,
outcome TEXT NOT NULL,
);
CREATE TABLE forms (
id INT NOT NULL PRIMARY_KEY AUTO_INCREMENT,
position_id INT NOT NULL FOREIGN_KEY REFERENCES job_positions(id)
creator_id INT NOT NULL FOREIGN_KEY REFERENCES users(id)
);
通过这种方式,用户与job_positions和表格具有1-N关系;和job_positions与表单具有1-N关系。换句话说,job_positions属于用户;和表格属于用户和职位。