46 lines
1.3 KiB
SQL
46 lines
1.3 KiB
SQL
CREATE TABLE "Guardians" (
|
|
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
|
|
"FirstName" TEXT NOT NULL,
|
|
"LastName" TEXT NOT NULL,
|
|
"PhoneNumber" INTEGER NOT NULL,
|
|
"EmailAddress" TEXT NOT NULL,
|
|
"PinNumber" INTEGER NOT NULL UNIQUE,
|
|
"isAdmin" INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE "Children" (
|
|
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
|
|
"FirstName" TEXT NOT NULL,
|
|
"LastName" TEXT COLLATE nocase NOT NULL,
|
|
"DOB" TEXT NOT NULL,
|
|
"address" TEXT NOT NULL,
|
|
"race" TEXT NOT NULL,
|
|
"gender" TEXT NOT NULL,
|
|
"photo" BLOB
|
|
);
|
|
|
|
CREATE TABLE "Attendence" (
|
|
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
|
|
"child_id" INTEGER NOT NULL,
|
|
"guardian_id" INTEGER NOT NULL,
|
|
"in_out" TEXT NOT NULL,
|
|
"timestamp" TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY("child_id") REFERENCES "Children"("id"),
|
|
FOREIGN KEY("guardian_id") REFERENCES "Guardians"("id")
|
|
);
|
|
|
|
CREATE TABLE "Authorized_Guardians" (
|
|
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
|
|
"child_id" INTEGER NOT NULL,
|
|
"guardian_id" INTEGER NOT NULL,
|
|
FOREIGN KEY("child_id") REFERENCES "Children"("id"),
|
|
FOREIGN KEY("guardian_id") REFERENCES "Guardians"("id")
|
|
);
|
|
|
|
CREATE TRIGGER trgUpdateTimestampToLocal AFTER INSERT ON Attendence
|
|
BEGIN
|
|
UPDATE Attendence
|
|
SET timestamp = datetime(timestamp, 'localtime')
|
|
WHERE id = new.id;
|
|
END;
|