# Script to create the BabyTalkWebLibrary database tables
# copyright 2004 Bert Szoghy
# webmaster@quadmore.com

# This database design was conceived for maximum reporting possibilities

# We will provide a dropdown list of authors to choose from
# when submitting a new book through the web application
CREATE TABLE Author (
author_id INT not null,
lastname VARCHAR(40) not null,
firstname VARCHAR(40) not null,
initial VARCHAR(4) null,
biography BLOB null,
primary key (author_id)
);

# We will only use two values, but you can create your own.
# Be warned that if you do, you will need to modify BabyTalkWeb's JTree  populating Java code,
# as it has "fiction" and "nonfiction" hardcoded in
CREATE TABLE Category (
category_id INT not null,
description VARCHAR(100) not null,
primary key (category_id)
);


INSERT INTO Category
(category_id, description)
VALUES (1,'fiction');


INSERT INTO Category
(category_id, description)
VALUES (2,'nonfiction');


# This will store the actual book text
# The notes column allows for historical comments,
# but will not be used by the current version of BabyTalk Web
CREATE TABLE Book (
book_id INT not null,
category_id INT not null,
author_id INT not null,
contributor_id INT not null,
title BLOB not null,
content MEDIUMBLOB not null,
notes BLOB null,
primary key (book_id)
);

# This table for the person out on the Internet that submits a text to the database
# The username is the courriel column ("email" in French)
# The password is the mot_de_passe column ("password" in French)
CREATE TABLE Contributor (
contributor_id INT not null,
user_role_id INT not null,
lastname VARCHAR(40) not null,
firstname VARCHAR(40) not null,
initial VARCHAR(4) null,
courriel VARCHAR(200) not null,
mot_de_passe VARCHAR(25) not null, 
biography BLOB null,
primary key (contributor_id)
);

# Defines user roles, we could define more if needed
CREATE TABLE UserRole (
user_role_id INT not null,
description VARCHAR(100) not null,
primary key (user_role_id)
);

INSERT INTO UserRole
(user_role_id, description)
VALUES (1,'Administrateur');


INSERT INTO UserRole
(user_role_id, description)
VALUES (2,'Commun_mortel');

# When you slog in and submit a text, the Administrator needs to approve it
# in order for it to become visible to others
CREATE TABLE Status (
status_id INT not null,
description VARCHAR(100) not null,
primary key (status_id)
);

INSERT INTO Status 
(status_id,description)
VALUES (1,'submitted pending review');

INSERT INTO Status 
(status_id,description)
VALUES (2,'rejected');

INSERT INTO Status 
(status_id,description)
VALUES (3,'approved');

# This cross-reference table tracks each text submitted
CREATE TABLE Submission (
submission_id INT not null,
status_id INT not null,
contributor_id INT not null,
book_id INT not null,
notes BLOB null,
primary key (submission_id)
);

SHOW TABLES;