SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Aug 2007
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database Design Help - Newbie

    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

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,182
    Mentioned
    16 Post(s)
    Tagged
    4 Thread(s)
    Code SQL:
    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.

  3. #3
    SitePoint Member
    Join Date
    Aug 2007
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank You!
    This is a little more then I needed however very helpful.

    Thank you for your time!!!
    Tim


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •