DROP DATABASE IF EXISTS dbjoinourteam;

CREATE DATABASE dbjoinourteam;

USE dbjoinourteam;

CREATE TABLE tbl_admin (
adminid tinyint(3) unsigned NOT NULL auto_increment,
adminusername varchar(15) NOT NULL,
adminpassword varchar(15) NOT NULL,
PRIMARY KEY (adminid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO tbl_admin (adminid, adminusername, adminpassword)
VALUES (1,'Smooby','#1Sm00bie#1');

CREATE TABLE tbl_jobopenings (
jobid int(10) unsigned NOT NULL auto_increment,
jobname varchar(30) NOT NULL,
PRIMARY KEY USING BTREE (jobid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE tbl_applicantinfo (
applicantid int(10) unsigned NOT NULL auto_increment,
lastname varchar(255) NOT NULL,
middleinitial varchar(1) NULL,
firstname varchar(255) NOT NULL,
email varchar(100) default NOT NULL,
emailapproved enum('n','y') NOT NULL default 'n',
cellphone varchar(14) NULL,
workphone varchar(14) NULL,
homephone varchar(14) NULL,
resume varchar(MAX) NOT NULL,
positionapplyingfor int(10) unsigned NOT NULL,
applicationdate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (applicantid),
KEY FK_tbl_applicantinfo_1 USING BTREE (positionapplyingfor),
CONSTRAINT FK_tbl_applicantinfo_1 FOREIGN KEY (positionapplyingfor) REFERENCES tbl_jobopenings (jobid) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

You know what would really help..?

If you ran each statement separately and told us which one(s), if any, are causing you the problem.

The needle is found much easier if you know which haystack it is in.

there's an index missing on the foreign key

Aww Rudy, where's the fun in that

what happens if Todd applies for a second position?

this design is gonna make him enter all his name and address crap again

Please tell me how to make it better. This is the first SQL database I've attempted to create. I have no experience with SQL at all.

remove positionapplyingfor from the applicants table, and add a positionapplyingfor table, consisting of two columns: jobid and applicantid

and please remove the tbl_ prefixes from your table names

OK. How then do I need to write the script so that the respective tables get called to retrieve the data?

which data are you interested in retrieving?

I have a web page that I'm writing in PHP and I want it to be able to accept applications for job openings, so I'd like applicants to be able to choose a job opening from a drop down list and store which jobs they have applied for with their personal info and resume.

that's rather an open-ended question, don't you think?

good luck with your project

there are many, many queries involved in building an application like that

please don't hesitate to post again when you have a specific problem

My specific question is how do I relate the applicants' personal info with the job(s) they've applied for? In other words, how do I reference the jobid and the applicantid in the positionapplyingfor table to the jobid in the jobopenings table and the applicantid to the applicantinfo table?

OK. I changed the code and got the following errors

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ') ENG
INE=InnoDB DEFAULT CHARSET=latin1' at line 14
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'KEY F
K_jobapplyingfor_2 USING BTREE (applicantid),
CONSTRAINT FK_jobapplyingfor_' at line 6

DROP DATABASE IF EXISTS dbjoinourteam;

CREATE DATABASE dbjoinourteam;

USE dbjoinourteam;

CREATE TABLE admin (
adminid tinyint(3) unsigned NOT NULL auto_increment,
adminusername varchar(15) NOT NULL,
adminpassword varchar(15) NOT NULL,
PRIMARY KEY (adminid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO admin (adminid, adminusername, adminpassword)
VALUES (1,'Smooby','#1Sm00bie#1');

CREATE TABLE jobopenings (
jobid int(10) unsigned NOT NULL auto_increment,
jobname varchar(30) NOT NULL,
PRIMARY KEY USING BTREE (jobid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE applicantinfo (
applicantid int(10) unsigned NOT NULL auto_increment,
lastname varchar(255) NOT NULL,
middleinitial varchar(1) NULL,
firstname varchar(255) NOT NULL,
email varchar(100) NOT NULL,
emailapproved enum('n','y') NOT NULL default 'n',
cellphone varchar(14) NULL,
workphone varchar(14) NULL,
homephone varchar(14) NULL,
resume varchar(65000) NOT NULL,
applicationdate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (applicantid),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE jobapplyingfor (
jobid int(10) unsigned NOT NULL,
applicantid int(10) unsigned NOT NULL,
KEY FK_jobapplyingfor_1 USING BTREE (jobid),
CONSTRAINT FK_jopapplyingfor_1 FOREIGN KEY (jobid) REFERENCES jobopenings (jobid) ON DELETE CASCADE ON UPDATE CASCADE
KEY FK_jobapplyingfor_2 USING BTREE (applicantid),
CONSTRAINT FK_jobapplyingfor_2 FOREIGN KEY (applicantid) REFERENCES applicantinfo (applicantid) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

What is the correct syntax for the lines that produced the errors? Also indexing was mentioned, what does that do and how do I do it?

you have a dangling comma here: PRIMARY KEY (applicantid),

for indexing, see here: optimizing mysql