brotherhood_of_LAN - 6:18 pm on Oct 10, 2012 (gmt 0)

I haven't used foreign keys very often so someone else may be able to offer comment on them, I don't think they'd be suitable for my suggestion of splitting the users up into different tables.

Procedures [dev.mysql.com] are great for keeping the logic of these kind of operations. Here's a rough example that'd delete from both tables, all you have to do is pass the userid to it. Here's a very rough example.

CREATE TABLE IF NOT EXISTS `user_credentials` (
`userid` int(10) unsigned NOT NULL,
`usertype` enum('agents','leads','partners') NOT NULL,
PRIMARY KEY (`userid`)
) ENGINE=MyISAM;

CREATE TABLE IF NOT EXISTS `user_agents` (
`userid` int(10) unsigned NOT NULL,
PRIMARY KEY (`userid`)
) ENGINE=MyISAM;

CREATE TABLE IF NOT EXISTS `user_leads` (
`userid` int(10) unsigned NOT NULL,
PRIMARY KEY (`userid`)
) ENGINE=MyISAM;

CREATE TABLE IF NOT EXISTS `user_partners` (
`userid` int(10) unsigned NOT NULL,
PRIMARY KEY (`userid`)
) ENGINE=MyISAM;

INSERT INTO `user_credentials` (`userid`, `usertype`) VALUES(1, 'agents'),(2, 'leads'),(3, 'partners'),(4, 'partners');
INSERT INTO `user_agents` (`userid`) VALUES (1);
INSERT INTO `user_leads` (`userid`) VALUES (2);
INSERT INTO `user_partners` (`userid`) VALUES (3,4);


DELIMITER //
CREATE PROCEDURE `delete_user` (IN userid INTEGER UNSIGNED)
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY DEFINER
BEGIN
SET @sql = concat('SET @usertype = (SELECT usertype FROM user_credentials WHERE userid = ',userid,')');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @sql = concat('DELETE user_credentials,user_',@usertype,' FROM user_credentials
INNER JOIN user_',@usertype,' ON user_credentials.userid = user_',@usertype,'.userid
WHERE user_credentials.userid = ',userid);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
//
DELIMITER ;


So if you wanted to delete the user with ID 1, you can use 'CALL delete_user(1);'

Using multiple tables has its pro's and con's but is easily manageable. The main thing is to consider how much data each of your user types would have in common IMO.