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,
);
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