Jump to content

medical specialties table


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 post
Share on other sites

nobody?

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...