Database Design Help - Newbie

[timwhelan]

okay I'll try to explain an best I can...

I am building a database with education certificates, 4 certificates. Each certificate has 4 courses. Each certificate will be offered 4 times at different dates. An applicant can sign up for courses at different times. So, Round 1 Certificate 1 and Course 1 & 2. Then they can come in Round 2 and take Certificate 1 - Course 3 & 4 and so on...

How can I set up the DB. I have a Rounds table: 4 rounds. I have a Certificates Table: 4 Certificates and Each Certificate has 4 Courses. I am confusing myself being newer to this.

If there is anything that will help, a link to info, a hey think about it this way...

Anything to get to think more clearly would be great!

Thanks
Tim

[oddz]

CREATE TABLE CERTIFICATES (
	certificates_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT
	,title VARCHAR(128) NOT NULL
	,PRIMARY KEY(certificates_id)
) ENGINE = MYISAM;
 
CREATE TABLE COURSES (
	courses_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT
	,title VARCHAR(128) NOT NULL
	,PRIMARY KEY(courses_id)
) ENGINE = MYISAM;
 
CREATE TABLE CERTIFICATES_TO_COURSES (
	certificates_id TINYINT UNSIGNED NOT NULL
	,courses_id SMALLINT UNSIGNED NOT NULL
	,PRIMARY KEY(certificates_id,courses_id)
) ENGINE = MYISAM;
 
CREATE TABLE PERSONS (
	persons_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
	,first_name VARCHAR(48) NOT NULL
	,middle_name VARCHAR(48) NULL
	,last_name VARCHAR(48) NOT NULL
	,PRIMARY KEY(persons_id)
) ENGINE = MYISAM;
 
CREATE TABLE PERSONS_TO_COURSES (
    ,persons_to_courses_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
	,persons_id BIGINT UNSIGNED NOT NULL
	,courses_id SMALLINT UNSIGNED NOT NULL
	,persons_types_id TINYINT UNSIGNED NOT NULL
	,PRIMARY KEY(persons_courses_id)
	,UNIQUE KEY(persons_id,courses_id)
);
 
CREATE TABLE PERSONS_TYPES (
	persons_types_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT
	,TYPE VARCHAR(24) NOT NULL
	,PRIMARY KEY(persons_types_id)
) ENGINE = MYISAM;

That is where I would begin in terms of a schema.

The persons types table can be used to define types of people such as; student, teacher, student teacher, etc. This way the schema support students that become teachers, teachers that enroll in a class, etc.

[timwhelan]

Thank You!
This is a little more then I needed however very helpful.

Thank you for your time!!!
Tim