storing username

[Dimitris Papageorgiou]

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.