storing username
Posted by: Dimitris Papageorgiou
Date: September 23, 2014 10:19AM

I am building a web app that stores appointments(it is a booking app in other words).

In this app either the user can book an appointment by himself through the app or he can make a phone call at which case a staff member will make the booking.

Having said the above the are 3 scenarios here:

The staff makes the booking for a non-registered user.

The staff makes the booking for a registered user(which just opt to call instead of using the web app)

A registered user just uses the web app to make a booking-decided not to call.

The table where the appointments are stored is this:

CREATE TABLE `appointments` (
`apID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`Bookfrom` mediumint(11) unsigned DEFAULT NULL,
`bookedfor` mediumint(11) unsigned DEFAULT NULL COMMENT 'σε αυτή την στήλη αναγράφονται τα ραντεβού για τον οποίον κλείνονται τα ραντεβού και εμφανίζονται στο backend του bookedfor business_user',
`appont_close_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`name` varchar(45) DEFAULT NULL,
`startDate` int(50) NOT NULL,
`endDate` int(50) DEFAULT NULL,
`apps_origin` enum('frontend','backend') NOT NULL,
`staffID` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`apID`),
KEY `fk_appointments_user1_idx` (`Bookfrom`),
KEY `bokkedfor` (`bookedfor`),
KEY `fk_appointments_staff1_idx` (`staffID`),
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`),
CONSTRAINT `appointments_ibfk_3` FOREIGN KEY (`staffID`) REFERENCES `staff` (`staff_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

There is also a users table:

CREATE TABLE `users` (
`user_ID` mediumint(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`lastname` varchar(45) NOT NULL,
`email` varchar(255) DEFAULT NULL,
`password` varchar(100) DEFAULT NULL,
`hash` varchar(32) DEFAULT NULL,
`usertype` enum('regular_user','buz_user','superadmin','reg_buz_user','deleted') DEFAULT NULL,
`Reg_date_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`user_ID`),
KEY `fk_users_usertype1_idx` (`usertype`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8

And here is my issue-as apply to the 3 scenarios:

If the staff makes the booking for a non-registered user,(as the case is now)his name is stored in the name column.NO PROBLEM HERE

If the staff makes the booking for a registered user of the app-the usual is that in the manes column there must be a userID which points to the users table(foreign key)....the names column though(in the appointments table) is of the VARCHAR type which means no userID(which is INT) can be stored there.PROBLEM HERE.

By solving the above problem I am "covered" also for the 3rd scenario-I think-so my focus of attention is on the above

What can I do?I have thought various solutions but I do not want to list them so as to avoid making the topic very lengthy.

What do you propose?

Thanks.


P.S In the appointments table there is a bookfrom column,currently is not used,I add it in the table though cause it might be useful when the time comes to address the above issue...and the time is now as you guess.

Options: ReplyQuote


Subject
Written By
Posted
storing username
September 23, 2014 10:19AM
September 25, 2014 04:13PM
September 26, 2014 01:15PM
September 27, 2014 12:50AM
September 29, 2014 11:51AM
September 29, 2014 12:06PM
September 29, 2014 12:11PM
September 29, 2014 12:13PM
September 29, 2014 03:21PM
September 29, 2014 12:36PM


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.