new advice database format.

redarrow · July 13, 2012

Advance thank you.

I got a new project and it worrying me.

In one of the database one row got to have 5 conditions.

example a user might be in charge of 5 different classes, what you recommend as the database format.

example.

when the user joins, do i just add 5 rows to the database, and let them add the class descriptions , do i keep the row null or a zero as default.

looks like ill need it for the teacher subjects as well.

id:

teacher_username:

teacher_surname:

teacher_email:

teacher_classes:

teacher_class_name 1

teacher_class_name 2

teacher_class_name 3

teacher_class_name 4

teacher_class_name 5

teacher_subject_name 1

teacher_subject_name 2

teacher_subject_name 3

teacher_subject_name 4

teacher_subject_name 5

teacher_date_joined

teacher_active

thanks all.

redarrow · July 13, 2012

i made a form that the user can supply the number of classes and number of subjects the input box's show, but the user has to submit to get the result then enter info.

any other ideas so no submit needed to get the input needed.



<form method="POST" action"">

Name <br />
<input type="text" name="name">
<br/ >Surname<br />
<input type="text" name="surname">
<br />Email Address<br />
<input type="text" name="email_address">
<br />Number of clases you teach <br />

<select name="num_of_classes">
<?php 
for($i=1; $i<6; $i++)
{ 
echo"<option value='$i'>$i</option>";
}
?>
</select>
<?php
for($i=1; $i<@$_POST['num_of_classes']+1; $i++){
echo"<br />class $i<input type='text' name='class[]'><br />";
}
?>


<br />Number of subjects you teach <br />
<select name="num_of_subjects">
<?php 
for($i=1; $i<6; $i++)
{ 
echo"<option value='$i'>$i</option>";
}
?>
</select>

<?php
for($i=1; $i<@$_POST['num_of_subjects']+1; $i++){
echo"<br />Subject $i<input type='text' name='subject[]'><br />";
}
?>

<br /><br />
<input type="submit" name="submit" value-"submit">

</form>

Barand · July 13, 2012

Looking at your form, it looks as though the "user" is the "teacher". Is this assumption correct?

If it is, you have three entities, namely Teacher, Class, Subject.

+-------------+ +--------------+ +--------------+

+-------------+ +--------------+ +--------------+

| id | | id | | id |

| surname | | ??? | | ??? |

| email | +--------------+ +--------------+

+-------------+

Now you have to ask what are the data elements you need to store for each of these. For "teacher" we have name, email etc and names for each of the other two. Any others?

The big question then is "What are the relationships between these entities?"

We already know

a teacher teaches 1 or more classes

a teacher teaches 1 or more subjects

Are these true also:

a subject is taught by by 1 or more teachers?

a class can be taught by 1 or more teachers.?

What is the relationship between class and subject?

When you have analysed your data in this way you can begin to design your properly normalised table structure.

redarrow · July 13, 2012

ill sort the database out like you say i need to think.

subject = subject the teacher can teach.

class= the class name the teacher is form tutor to.

it true to say in the uk a teacher can teach many subjects at any period of the day.

it also true in the uk a teacher teaches more then one class name with over 35 children in.

why is the subject[] array not showing all it results.

dont forget i am only testing every think.

try the form please.

<?php

if(@$_POST['submit']){

echo " name: {$_POST['name']}";
echo "<br />";
echo " surname: {$_POST['surname']}";
echo"<br />";
echo "email address: {$_POST['email_address']}";

foreach($_POST['subject'] as $subjects){

echo"<br />";

echo "subject: $subjects";

foreach($_POST['class'] as $classs){

echo"<br />";

echo " class: $classs";

exit;

} 
}
  }
?>



<?php 
@$result1=$_POST['name'];
@$result2=$_POST['surname'];
@$result3=$_POST['email_address'];
?>

<form method="POST" action"">
Teachers Name <br />
<input type="text" name="name" value="<?php echo $result1; ?>">
<br/ >Teachers Surname<br />
<input type="text" name="surname" value="<?php echo $result2; ?>">
<br />Teachers Email Address<br />
<input type="text" name="email_address" value="<?php echo $result3; ?>">
<br />Number of student clases names, you teach <br />

<select name="num_of_classes">
<?php 
for($i=1; $i<6; $i++)
{ 
echo"<option value='$i'>$i</option>";
}
?>
</select>
<?php
for($i=1; $i<@$_POST['num_of_classes']+1; $i++){
echo"<br />class $i<input type='text' name='class[]'><br />";
}
?>


<br />Number of subjects you teach <br />
<select name="num_of_subjects">
<?php 
for($i=1; $i<6; $i++)
{ 
echo"<option value='$i'>$i</option>";
}
?>
</select>

<?php
for($i=1; $i<@$_POST['num_of_subjects']+1; $i++){
echo"<br />Subject $i<input type='text' name='subject[]'><br />";
}
?>

<br />
<input type="submit" value="Please submit your chose">
<br /><br />
<input type="submit" name="submit" value="Save Information">


</form>

Barand · July 13, 2012

why is the subject[] array not showing all it results.

Because you have an "exit;" in the loop.

Don't you think you are putting the cart before the horse creating forms before you have your data organised?

Also, I wouldn't rely on text entry for subjects. If 5 teachers enter, say,

English literature

English lit

English Lit

Eng Lit

Eng. Lit.

You now have 5 subject names instead of just 1. Give them a selection menu.

redarrow · July 13, 2012

noted the select .

but just out of curiosity if this database struckture will work.

i got so far.

Teacher table.

id:

teacher_id

teacher_username:

teacher_surname:

teacher_email:

teacher_classes:

teacher_class_name 1

teacher_class_name 2

teacher_class_name 3

teacher_class_name 4

teacher_class_name 5

teacher_subject_name 1

teacher_subject_name 2

teacher_subject_name 3

teacher_subject_name 4

teacher_subject_name 5

teacher_date_joined

teacher_active

Student Table

id:

student_id:

student_class_name:

student_name:

student_surname:

student_email:

student_valadation_key:

student_joined:

student_active:

home_work_table

id:

home_work_id:

home_work_class_name:

home_work_name:

home_work_descrition:

home_work_start_date:

home_work_end_date:

teachers_name:

student_name:

Barand · July 13, 2012

From the info that I've gleaned so far I have come up with a data model for you. I introduced a "lesson" entity to link subject,class and teacher, where a lesson is a subject taught to a class at a specific time and place by a teacher. Homework is allocated in a lesson to a student.

I'm attaching the Entity Relationship Model as a png file

Barand · July 13, 2012

PS

and here's a script to create the tables

-- -----------------------------------------------------
-- Table `teacher`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `teacher` (
  `teacher_id` INT(11) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NULL DEFAULT NULL ,
  `surname` VARCHAR(45) NULL DEFAULT NULL ,
  `email` VARCHAR(45) NULL DEFAULT NULL ,
  PRIMARY KEY (`teacher_id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `class`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `class` (
  `class_id` INT(11) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NULL DEFAULT NULL ,
  `tutor_id` INT(11) NOT NULL ,
  PRIMARY KEY (`class_id`) ,
  INDEX `fk_class_teacher1` (`tutor_id` ASC) ,
  CONSTRAINT `fk_class_teacher1`
    FOREIGN KEY (`tutor_id` )
    REFERENCES `teacher` (`teacher_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `subject`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `subject` (
  `subject_id` INT(11) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NULL DEFAULT NULL ,
  PRIMARY KEY (`subject_id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `lesson`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `lesson` (
  `lesson_id` INT(11) NOT NULL AUTO_INCREMENT ,
  `room` INT(11) NULL DEFAULT NULL ,
  `start_time` TIME NULL DEFAULT NULL ,
  `end_time` TIME NULL DEFAULT NULL ,
  `teacher_id` INT(11) NOT NULL ,
  `subject_id` INT(11) NOT NULL ,
  `class_id` INT(11) NOT NULL ,
  PRIMARY KEY (`lesson_id`) ,
  INDEX `fk_lesson_teacher` (`teacher_id` ASC) ,
  INDEX `fk_lesson_subject1` (`subject_id` ASC) ,
  INDEX `fk_lesson_class1` (`class_id` ASC) ,
  CONSTRAINT `fk_lesson_teacher`
    FOREIGN KEY (`teacher_id` )
    REFERENCES `teacher` (`teacher_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_lesson_subject1`
    FOREIGN KEY (`subject_id` )
    REFERENCES `subject` (`subject_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_lesson_class1`
    FOREIGN KEY (`class_id` )
    REFERENCES `class` (`class_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `student`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `student` (
  `student_id` INT(11) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NULL DEFAULT NULL ,
  `surname` VARCHAR(45) NULL DEFAULT NULL ,
  `email` VARCHAR(45) NULL DEFAULT NULL ,
  `validation_key` VARCHAR(255) NULL DEFAULT NULL ,
  `joined_date` DATE NULL DEFAULT NULL ,
  `active` TINYINT(4) NULL DEFAULT NULL ,
  `class_id` INT(11) NOT NULL ,
  PRIMARY KEY (`student_id`) ,
  INDEX `fk_student_class1` (`class_id` ASC) ,
  CONSTRAINT `fk_student_class1`
    FOREIGN KEY (`class_id` )
    REFERENCES `class` (`class_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `homework`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `homework` (
  `hwork_id` INT(11) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NULL DEFAULT NULL ,
  `description` VARCHAR(255) NULL DEFAULT NULL ,
  `start_date` DATE NULL DEFAULT NULL ,
  `end_date` DATE NULL DEFAULT NULL ,
  `lesson_id` INT(11) NOT NULL ,
  `student_id` INT(11) NOT NULL ,
  PRIMARY KEY (`hwork_id`) ,
  INDEX `fk_homework_lesson1` (`lesson_id` ASC) ,
  INDEX `fk_homework_student1` (`student_id` ASC) ,
  CONSTRAINT `fk_homework_lesson1`
    FOREIGN KEY (`lesson_id` )
    REFERENCES `lesson` (`lesson_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_homework_student1`
    FOREIGN KEY (`student_id` )
    REFERENCES `student` (`student_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

Sign In

new advice database format.

Recommended Posts

redarrow

Link to comment

Share on other sites

redarrow

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

redarrow

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

redarrow

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information