referential integrity / lookups

[dba-dev]

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;

[timothybard]

You should use the type_ID in the schools table. This will allow the name of the type to change without affecting the data. Granted, in this case, I doubt the name will change, so you should be safe using the name instead of the id, but it is good database practice to use the id for database purposes and descriptions (names) only for user output.

As far as building something to allow the user to select a school type for each school... I think it is just something that you will need to take the time to do.

As far as referential integrity... of course it is important to have data that refers to other records correctly, but I would say it is not necessary to have referential integrity as long as you, the programmer, keep this in mind while programming the database and ensure that the lack of referential integrity doesn't cause any problems down the road. There are ideals in database programming, but things such as deadlines and cost requirements do get in the way.

[dba-dev]

You should use the type_ID in the schools table. This will allow the name of the type to change without affecting the data. Granted, in this case, I doubt the name will change, so you should be safe using the name instead of the id, but it is good database practice to use the id for database purposes and descriptions (names) only for user output.

As far as building something to allow the user to select a school type for each school... I think it is just something that you will need to take the time to do.

As far as referential integrity... of course it is important to have data that refers to other records correctly, but I would say it is not necessary to have referential integrity as long as you, the programmer, keep this in mind while programming the database and ensure that the lack of referential integrity doesn't cause any problems down the road. There are ideals in database programming, but things such as deadlines and cost requirements do get in the way.

Thanks for your reply. Have one question/comment with reference to your last comment. Will it not be the case that left to their own input a user will often input (bad?) datum. For example .... enter grade school instead of elementary school and the just plan misspelling errors, etc. So, is it not incumbent on the designer/programmer/dba to create rf's everywhere they are applicable? If this is true it could, will make the project much more daunting. As you might decipher I am struggling with this dilemma.

[timothybard]

Cleaning up a database with bad data will usually cost more money and time than programming a database that will not allow users to add bad data. Therefore, I usually recommend programming the database "properly" the first time, even though it adds a level of complexity to the programming.

Having said that, if the feature or data is not used often, then perhaps it would be more time and cost efficient to not worry about referential integrity in all situations. Of course referential integrity is the preference, but if there is a deadline that needs to be met, having a database that lacks referential integrity in a few instances is better than not releasing the database.

This is where knowing how the database is going to be used is important.

[dba-dev]

Thanks for your advise, it is appreciated.