Jump to content

medical specialties table


jimfog
 Share

Recommended Posts

I am building an app where a table holds business users-businesses data:

 

CREATE TABLE `business_users` (
  `crID` mediumint unsigned NOT NULL,

 `bus_user_type` smallint DEFAULT NULL,
 `pack_selected` smallint unsigned NOT NULL,
  `sched_entered` tinyint(1) DEFAULT 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

In the table above I have ommited some columns for brevity reasons.

As you see there is a bus_user_type column....a business user might be for example a doctor a hair stylist a car shop....etc

The doctors are a special case cause I must also store theri specialty(dentist,cardioloist etc)....for this reason I have concluded to the following sheme-tell me what you think:

 

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

In the two above tables I probably must change their names…also see a diagramm of the above tables.

diagramm link

Link to comment
Share on other sites

nobody?

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...