need advice on where to place column
Here is the situation:
I am building an app where business users will list their services offered,with their prices and with the option of having to choose whether this price will be visible from the user(customer) of the app.So I have 3 tables:
1.details about business users are stored here:
CREATE TABLE `business_users` (
`crID` mediumint(11) unsigned NOT NULL,
`comp_name` varchar(45) DEFAULT NULL,
`address` varchar(90) DEFAULT NULL,
`url` varchar(90) DEFAULT NULL,
`phone` varchar(20) DEFAULT NULL,
`city` varchar(100) DEFAULT NULL,
`municipality` varchar(100) DEFAULT NULL,
`bus_user_type` smallint(2) DEFAULT NULL,
`same_d_appt` tinyint(4) DEFAULT NULL,
`f_words` tinytext,
`apps_timeslot` tinyint(3) unsigned DEFAULT NULL,
`prices_visibility` tinyint(1) unsigned DEFAULT NULL,
`pack_selected` smallint(6) 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`) ON DELETE NO ACTION ON UPDATE NO ACTION,
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 above table you see that price visibility is stored here BUT I AM NOT SURE IF THIS IS THE CORRECT CHOICE.
The other two tables:
this tables holds the services.
CREATE TABLE `services_list` (
`serviceID` int(11) NOT NULL AUTO_INCREMENT,
`price` decimal(5,2) DEFAULT NULL,
`servicename` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
`duration` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`serviceID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='it lists the possible services'
and this one links business users with services:
CREATE TABLE `busers_services` (
`b_user` mediumint(11) unsigned NOT NULL,
`serviceID` int(11) NOT NULL,
PRIMARY KEY (`serviceID`,`b_user`),
KEY `fk_busers_services_business_users1_idx` (`b_user`),
KEY `fk_busers_services_services_list1_idx` (`serviceID`),
CONSTRAINT `fk_busers_services_business_users1` FOREIGN KEY (`b_user`) REFERENCES `business_users` (`crID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_busers_services_services_list1` FOREIGN KEY (`serviceID`) REFERENCES `services_list` (`serviceID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='it lists the services offered by businesses'
So the question is from these three tables where do you think is the best one to store price visibility?