Re: staff table-howo to connect with bookings and bus. users table
Posted by: Dimitris Papageorgiou
Date: February 21, 2014 08:50AM

Here is the show create table result for all 4 tables.
For the staff table:
CREATE TABLE `staff` (
`name` varchar(45) NOT NULL,
`staff_b_user` int(11) NOT NULL,
`staff_ID` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`staff_ID`),
KEY `fk_staff_business_users1_idx` (`staff_b_user`),
CONSTRAINT `fk_staff_business_users1` FOREIGN KEY (`staff_b_user`) REFERENCES `business_users` (`crID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

For the appointments table:


CREATE TABLE `appointments` (
`apID` int(11) NOT NULL AUTO_INCREMENT,
`Bookfrom` int(11) DEFAULT NULL COMMENT 'σε αυτή την στήλη είναι όσοι κλεινουν το ραντεβού από το ιντερνετ και μη...όσοι είναι απο το ιντερνετ, registered δηλαδή, θα εμφανίζεται το ραντεβού στο frontend στην οθόνη τους\n',
`bookedfor` int(11) DEFAULT NULL COMMENT 'σε αυτή την στήλη αναγράφονται τα ραντεβού για τον οποίον κλείνονται τα ραντεβού και εμφανίζονται στο backend του bookedfor business_user',
`appont_close_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`name` varchar(45) DEFAULT NULL,
`startDate` varchar(45) DEFAULT NULL,
`apps_origin` enum('frontend','backend') DEFAULT NULL,
`staff_id` int(10) DEFAULT NULL,
PRIMARY KEY (`apID`),
KEY `fk_appointments_user1_idx` (`Bookfrom`),
KEY `bokkedfor` (`bookedfor`),
KEY `fk_appointments_staff1_idx` (`staff_id`),
CONSTRAINT `appointments_ibfk_1` FOREIGN KEY (`Bookfrom`) REFERENCES `users` (`user_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `appointments_ibfk_2` FOREIGN KEY (`bookedfor`) REFERENCES `users` (`user_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_appointments_staff1` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

For the Business Users table:
CREATE TABLE `business_users` (
`crID` int(11) NOT NULL,
`address` varchar(45) DEFAULT NULL,
`url` varchar(45) DEFAULT NULL,
`phone` varchar(20) DEFAULT NULL,
`city` varchar(100) DEFAULT NULL,
`municipality` varchar(100) DEFAULT NULL,
`bus_user_type` enum('1','2','3','4','5') CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`crID`),
KEY `fk_business_users_buz_usertype1_idx` (`bus_user_type`),
KEY `crID` (`crID`),
CONSTRAINT `fk_business_users_buz_usertype1` FOREIGN KEY (`bus_user_type`) REFERENCES `buz_usertype` (`Type_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_business_users_users1` FOREIGN KEY (`crID`) REFERENCES `users` (`user_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I hava made some things myself to solve the problem,that is why for example you will find a stafID column.
Stating the problem once more-briefly:

Every business user employs staff(hair salons for example).The goal is that when the user makes the appointment,the appointments table shows with what staff the appointments was closed-and of course with whom business user/owner the particular staff is associated with.Given the 3 tables above, what do you propose?

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.