Table Structure and foreign keys

Question

In mysql database, I have a total of 7 tables. Under normal circumstances an instructor belongs to only one academy but an academy can have many instructors. In the same way many courses can be taught in one academy. I am not keeping record of what instructor taught which course but what course is being taught at x or y academy. Also each academy has a main contact person and unlike the instructors, a contact person can be part of several academies. In the end, I would like to run a query where I can see the linked data to a specific academy of my choice.

Are the tables below structured to do store/display data in away that there is no repeated values of same instructors, courses, etc?

CREATE TABLE IF NOT EXISTS `academies` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(25) NOT NULL,
  `mou_id` int(11) default NULL,
  `academy_id` int(11) NOT NULL,
  `street_address` varchar(50) NOT NULL,
  `city` varchar(25) NOT NULL,
  `state` varchar(25) NOT NULL,
  `country` varchar(25) NOT NULL,
  `zipcode` varchar(5) NOT NULL,
  `telephone` varchar(15) NOT NULL,
  `comments` text,
  `last_contacted` date default NULL,
  PRIMARY KEY  (`id`)
) ;



CREATE TABLE IF NOT EXISTS `academy_courses` (
  `id` int(11) NOT NULL auto_increment,
  `academy_id` int(11) NOT NULL,
  `course_name` varchar(75) NOT NULL,
  `course_id` int(11) NOT NULL,
  `instructor_id` int(11) NOT NULL,
  `start_date` date default NULL,
  PRIMARY KEY  (`unique_id`),
  KEY `course_id` (`academy_id`,`course_id`)
);


CREATE TABLE IF NOT EXISTS `courses` (
  `course_id` int(11) NOT NULL auto_increment,
  `course_name` varchar(75) NOT NULL,
  PRIMARY KEY  (`course_id`)
);


INSERT INTO `courses` (`course_id`, `course_name`) VALUES
(1, 'Math'),
(2, 'English'),
(3, 'Science'),
(4, 'HIstory');


CREATE TABLE IF NOT EXISTS `instructors` (
  `instructor_id` int(11) NOT NULL auto_increment,
  `academy_id` int(11) NOT NULL,
  `instructor_name` varchar(50) NOT NULL,
  `instructor_phone` varchar(15) default NULL,
  `instructor_email` varchar(55) default NULL,
  PRIMARY KEY  (`instructor_id`),
  KEY `academy_id` (`academy_id`)
);


CREATE TABLE IF NOT EXISTS `last_contacted` (
  `talkedto_id` int(11) NOT NULL auto_increment,
  `academy_id` int(11) NOT NULL,
  `talkedto_name` varchar(50) NOT NULL,
  PRIMARY KEY  (`talkedto_id`),
  KEY `academy_id` (`academy_id`)
);


CREATE TABLE IF NOT EXISTS `main_contact` (
  `contact_id` int(11) NOT NULL auto_increment,
  `academy_id` int(11) NOT NULL,
  `contact_name` varchar(50) NOT NULL,
  `contact_phone` varchar(15) default NULL,
  `contact_email` varchar(55) default NULL,
  PRIMARY KEY  (`contact_id`),
  KEY `academy_id` (`academy_id`)
);



CREATE TABLE IF NOT EXISTS `main_contact_bridge` (
  `academy_id` int(11) NOT NULL,
  `contact_id` int(11) NOT NULL,
  PRIMARY KEY  (`contact_id`,`academy_id`),
  KEY `academy_id` (`academy_id`)
);

UPDATE : Based on Answer by Emmad Kareem

CREATE TABLE IF NOT EXISTS `person` (
 id int(11) NOT NULL auto_increment,
 `academy_id` int(11) NOT NULL,
 `person_name` varchar(50) NOT NULL,
 `person_email` varchar(50) default NULL, 
 `person_phone` varchar(15) default NULL,
 PRIMARY KEY (`id`),
 CONSTRAINT `academy_id` FOREIGN KEY (`academy_id`) REFERENCES `academy` (`academy_id`) ON DELETE CASCADE ON UPDATE CASCADE,
);

Answer 1

You have stated the following rules:

1-an instructor belongs to only one academy but an academy can have many instructors

2-In the same way many courses can be taught in one academy.

3-I am not keeping record of what instructor taught which course but what course is being taught at x or y academy

4-Also each academy has a main contact person and unlike the instructors, a contact person can be part of several academies.

Here are some of the issues that you might want to consider and fix.

A-Use singular form form table name. Ex: Academy

B- AcademyCource Table

The column definition:

  `instructor_id` int(11) NOT NULL,

Contradicts your rule #3

C- Course Table

The table definition does not have a FK reference to Academy table. This contradicts the requirements #2,3

D-Table LastContacted

You have not provided rules for this table in the requirements, but I would suggest that it would be associated with the contact person and not have the contact name as a free text, otherwise, what is the use of the contact person?

E-MainContact Table and Main_Contact_Bridge

Rule number 4 is:

4-A Each Acaddemy appoints 1 main contact

4-B Each contact can be appointed as main contact for 0,1 or more Academy

If you don't keep track of history appointments, the rule above does not require the Main_Contact_Bridge. The relationship becomes one-to-many with the one side being the contact and the many side being the academy.

If you want to track history, then Main_Contact_Bridge is justified but you need to add 2 columns to show start date and end date of the appointment. How would you enforce the rule that there is only 1 contact person for an Academy at a given point in time? This requires further analysis.

Another issue here is that if you have a separate contact table, and that contact is one of the instructors, then that person information will be entered 2 times in the system. That may be acceptable in cases, but in a good design you want to avoid this. One way to avoid this is to create a table called "Person" and add a column to indicate wheter that person is an instructor or a contact.

F-Instructor Table

Your design assumes that an instructor can work for 1 Academy only at a give point in time. If this is the case, then it is OK. If not, you have a many-to-many relationship that you need to address by a bridge table.

Also take in consideration the point raised above about the Contact.

E-You should use proper constrain/FK syntax. See for example: MySQL FK