Some design advise please. I have the following 2 tables. How far does one go with referential integrity. In the below example, the schools table has type_name (which is ... 2 elementary, 3 high school, 4 baccalaureate degree..... I have created a child/parent table reference between 'schools' and schools_type. I will now need to write a select/lookup app so the user can select the appropriate school_type_name for entry in 'schools' table. This is a lot of extra work..... where does on draw the line? Is this good referential integrity? Does one need to do this with many/any normalization fields? How does one determine whether one should use 'type_id' or 'type_name' in the schools table?
Regards and Thanks in advance.
CREATE TABLE `cccb_bc`.`schools` ( `school_id` int(11) NOT NULL, `member_id` int(4) NOT NULL, `school_name` varchar(50) NOT NULL, `school_type_id` int(2) NOT NULL, `school_city` varchar(40) NOT NULL, `school_state` varchar(2) NOT NULL, `school_zip` varchar(5) NOT NULL, `school_start_date` date DEFAULT NULL, `school_end_date` date DEFAULT NULL, `school_completed` varchar(1) NOT NULL, /* UNIQUE KEY `school_type_id` (`school_type_id`), */ KEY `fk_school_type_id` (`school_type_id`), CONSTRAINT `schools_types_fk` FOREIGN KEY (`school_type_id`) REFERENCES `school_types` (`school_type_id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `school_types` ( `school_type_id` int(2) NOT NULL AUTO_INCREMENT, `school_type_name` varchar(30) NOT NULL, PRIMARY KEY (`school_type_id`), UNIQUE KEY `school_type_name` (`school_type_name`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;