Foreign Keys

[eanderson]

I am fairly new at building databases, and am currently putting one together that works with PHP on my server. I run MySQL 5.1.30 and this is my current createtables.php file (which is still in construction):

 

$sql = "CREATE TABLE users( ".
  user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, ".
  user_login VARCHAR(20) UNIQUE NOT NULL, ".
  user_pass CHAR(40) NOT NULL, ".
  user_firstname VARCHAR(20) NOT NULL, ".
  user_lastname VARCHAR(20) NOT NULL, ".
  PRIMARY KEY (user_id)); ";

  $result = mysqli_query($sql, $life);
  if (!$result)
  {
   die('Could not create table: ' .mysql_error());
  }
  echo "Users table successfully created\n";
  
$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); ";
  INDEX (family_id); ";
  FOREIGN KEY (family_id) REFERENCES family (family_id); ";
  ) TYPE = INNODB; ";

  $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
  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 products( ".
  prod_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, ".
  prod_number VARCHAR(20) UNIQUE, ".
  prod_quantity INT NOT NULL, ".
  prod_cost DEC(6) NOT NULL, ".
  prod_msrp DEC(6), ".
  prod_price DEC(6) NOT NULL, ".
  prod_description TEXT, ".
  prod_distributor VARCHAR(25) NOT NULL, ".
  prod_lastedit DATE NOT NULL, ".
  prod_lastuser VARCHAR(20), ".
  PRIMARY KEY ( prod_id )); ";
  
  $result = mysqli_query($sql, $life);
  if (!$result)
  {
   die('Could not create table: ' .mysql_error());
  }
  echo "Product table successfully created\n";

 

 

What I'm having problems with are foreign keys. I know that some of my tables need to be linked to others, and tried to do so with the above code (See 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; ";

 

When I start scripting, this is what I am keeping in mind:

 

For each STUDENT must be connected to 1 FAMILY

- Some FAMILIES can have more than 1 STUDENT

 

For each CLASS must have 1 (sometimes more than one) STUDENT

For each STUDENT can have 1 CLASS or more

 

 

Am I on the right track? I am having a hard time figuring out where the remaining foreign keys should be placed.

[fenway]

Don't worry about one-to-one vs one-to-many at this stage... it doesn't matter for the constraints.