Storing the phones of the users
I have two tables...the one stores appointments and the other stores users:
Besides the users who will book appointments from the site there are also users that will book an appointmnet from the phone, my problem is that I want to store somewhere the phones(a requirement) of these users.
CREATE TABLE `appointments` (
`apID` int unsigned NOT NULL AUTO_INCREMENT,
`Bookfrom` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT ',
`bookedfor` mediumint unsigned NOT NULL ,
`appont_close_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`startDate` date NOT NULL,
`startime` time NOT NULL,
`endDate` date NOT NULL,
`endTime` time NOT NULL,
`apps_origin` enum('frontend','backend') NOT NULL,
`delete_back` tinyint unsigned DEFAULT NULL,
`delete_front` tinyint unsigned DEFAULT NULL,
`bookfromID` mediumint unsigned DEFAULT NULL,
`cancelled` tinyint unsigned NOT NULL DEFAULT '0',
`phone` varchar(20) DEFAULT NULL,
PRIMARY KEY (`apID`),
KEY `startDate` (`startDate`),
KEY `fk_bookFromID` (`bookfromID`),
KEY `bookedfor` (`bookedfor`),
CONSTRAINT `fk_bookedFor` FOREIGN KEY (`bookedfor`) REFERENCES `business_users` (`bus_user_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_bookFromID` FOREIGN KEY (`bookfromID`) REFERENCES `users` (`user_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE `users` (
`user_ID` mediumint unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(225) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`lastname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`usertype` tinyint unsigned NOT NULL,
`Reg_date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`registr_hash` char(32) DEFAULT NULL,
`active` tinyint unsigned NOT NULL DEFAULT '0',
`reset_pass_selector` char(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`reset_pass_verifier` char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`token_expire` int unsigned DEFAULT NULL,
`del_account_time` timestamp NULL DEFAULT NULL,
`FB_user` tinyint(1) DEFAULT NULL,
`Google_user` tinyint(1) DEFAULT NULL,
`FB_user_ID` bigint unsigned DEFAULT NULL,
`deleted` tinyint(1) NOT NULL DEFAULT '0',
`userPhone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
PRIMARY KEY (`user_ID`),
KEY `fk_users_user_type1_idx` (`usertype`),
KEY `email` (`email`),
CONSTRAINT `fk_users_user_type1` FOREIGN KEY (`usertype`) REFERENCES `user_type` (`usertype_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=311 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
So:
Do you think I should create a separate_table for these phone_users(to keep their phones and names) or I should put them in the users table(and use ENUM for example to distinguish them from site registered users).
That is one thing...
The other is that I must somehow prevent a double entry of the same phone user.
So what do you propose for these two problems?
In the appointmnets table I have a phone column...so that each booked appointmnet has a phone associated with it(along with other data)...the problem with this approach is that the same phone number will be duplicated many times since the same phone user will book appointmnets many times within a timeframe (hair salon for example about 1/month)
So,to recap where do I store phone users w their name and phone....and how to prevent double entry?
When the business goes to enter in the form the name/phone of the phone user how can I reliable check in the DB that the user is not already there...that problem becomes more pertinent when for example the business writes John or john...that will refer to the same person--without a reliable DB check duplication of entry will occur in the DB