Re: business users services
Posted by:
Rick James
Date: August 02, 2014 01:02PM
If I understand you correctly,
* Each user can provide many services, and
* Each service can be provided by many users
This is a "many-to-many" relationship. Such is best implemented by 3 tables -- one indexed by user, one by service, and one for the relationship:
CREATE TABLE `busers_services` (
`b_user` int(11) NOT NULL,
`serviceID` int(11) NOT NULL,
PRIMARY KEY (`b_user`,`serviceID`),
INDEX(serviceID)
) ENGINE=InnoDB
Notes:
* Note the secondary index to allow going 'the other direction'.
* I'm not a fan of FOREIGN KEYs, so I ignored them.
> do you think the 2nd table is redundant?
Absolutely not (assuming many-to-many)
If it is a 1-to-many (or many-to-1) relationship, then it absolutely is a waste.
Instead of INT SIGNED, consider MEDIUMINT UNSIGNED or SMALLINT UNSIGNED.