Re: separate table or no
After reading the resources you gave me I have concluded to the following scheme...tell me what you think:
CREATE TABLE `business_users` (
`crID` mediumint unsigned NOT NULL,
`--some cols ommited for reasons of brevity and readability
`pack_selected` smallint unsigned NOT NULL,
PRIMARY KEY (`crID`),
KEY `fk_business_users_buz_usertype1_idx` (`bus_user_type`),
KEY `pack` (`pack_selected`),
CONSTRAINT `fk_business_users_buz_usertype1` FOREIGN KEY (`bus_user_type`) REFERENCES `buz_usertype` (`Type_id`),
CONSTRAINT `fk_business_users_users1` FOREIGN KEY (`crID`) REFERENCES `users` (`user_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `pack_fk` FOREIGN KEY (`pack_selected`) REFERENCES `packages` (`package_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `doctors_specialties` (
`business_users_crID` mediumint unsigned NOT NULL,
`medical_specialties_specialty_ID` tinyint NOT NULL,
`speci_ID` mediumint NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`speci_ID`),
KEY `fk_doctors_specialties_business_users1_idx` (`business_users_crID`),
KEY `fk_doctors_specialties_medical_specialties1_idx` (`medical_specialties_specialty_ID`),
CONSTRAINT `fk_doctors_specialties_business_users1` FOREIGN KEY (`business_users_crID`) REFERENCES `business_users` (`crID`),
CONSTRAINT `fk_doctors_specialties_medical_specialties1` FOREIGN KEY (`medical_specialties_specialty_ID`) REFERENCES `medical_specialties` (`specialty_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `medical_specialties` (
`specialty_ID` tinyint NOT NULL,
`specialty_name` varchar(45) DEFAULT NULL,
PRIMARY KEY (`specialty_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8