This question is similar to one recently posted but I still can't figure out what I should do. I have a few different tables, most notably:
students
instructors
classes
families
Now, what I am trying to do is connect them so they work together. For instance:
Each STUDENT will only be related to 1 FAMILY
- Each FAMILY will be related to 1+ STUDENTS
Each CLASS will only have 1 INSTRUCTOR
- Each CLASS will also have 1+ STUDENTS
With this in mind, I created these tables:
$sql = "CREATE TABLE families( ".
family_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, ".
family_first VARCHAR(20) NOT NULL, ".
family_last VARCHAR(40) NOT NULL, ".
family_address VARCHAR(90) NOT NULL, ".
family_city VARCHAR(40) NOT NULL, ".
family_state CHAR(2) NOT NULL, ".
family_zip MEDIUMINT(5) NOT NULL, ".
family_home1 MEDIUMINT(3) UNSIGNED NOT NULL, ".
family_home2 MEDIUMINT(3) UNSIGNED NOT NULL, ".
family_home3 MEDIUMINT(3) UNSIGNED NOT NULL, ".
family_cell1 MEDIUMINT(3) UNSIGNED NOT NULL, ".
family_cell2 MEDIUMINT(3) UNSIGNED NOT NULL, ".
family_cell3 MEDIUMINT(3) UNSIGNED NOT NULL, ".
family_email VARCHAR(60) NOT NULL, ".
family_emergency TEXT NOT NULL, ".
PRIMARY KEY (family_id)); ";
$result = mysqli_query($sql, $life);
if (!$result)
{
die('Could not create table: ' .mysql_error());
}
echo "Family table successfully created\n";
$sql = "CREATE TABLE students( ".
student_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, ".
family_id INT NOT NULL, ".
student_firstname VARCHAR(20) NOT NULL, ".
student_lastname VARCHAR(40) NOT NULL, ".
student_dob DATE NOT NULL, ".
student_enrolldate DATE NOT NULL, ".
PRIMARY KEY (student_id)); ";
$result = mysqli_query($sql, $life);
if (!$result)
{
die('Could not create table: ' .mysql_error());
}
echo "Students table successfully created\n";
$sql = "CREATE TABLE classes( ".
class_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, ".
class_type VARCHAR(20) NOT NULL, ".
class_start NOT NULL, ".
class_day NOT NULL, ".
class_time NOT NULL, ".
class_duration
class_instructor
PRIMARY KEY (student_id)); ";
$result = mysqli_query($sql, $life);
if (!$result)
{
die('Could not create table: ' .mysql_error());
}
echo "Students table successfully created\n";
My question is, what is the best way to relate these databases? I've been trying to read and learn more, but am stuck. Do I use Foreign Keys? And if so, do I include the foreign key within an exsisting table? For example, the STUDENTS table:
$sql = "CREATE TABLE students( ".
student_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, ".
family_id INT NOT NULL, ".
student_firstname VARCHAR(20) NOT NULL, ".
student_lastname VARCHAR(40) NOT NULL, ".
student_dob DATE NOT NULL, ".
student_enrolldate DATE NOT NULL, ".
PRIMARY KEY (student_id); ";
INDEX (family_id); ";
FOREIGN KEY (family_id) REFERENCES family (family_id); ";
) TYPE = INNODB; ";
Or do I create a new table to show their relations? For example:
$sql = "CREATE TABLE student_to_family( ".
student_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, ".
family_id MEDIUMINT UNSIGNED NOT NULL, ".
PRIMARY KEY (student_id); ";
INDEX (family_id); ";
FOREIGN KEY (family_id) REFERENCES family (family_id); ";
) TYPE = INNODB; ";
Or is there another way that is completely going over my head?
