homepage Welcome to WebmasterWorld Guest from 54.196.24.103
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Website
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Table Design for Users - One table for each or single table?
tec4




msg:4505571
 6:20 pm on Oct 8, 2012 (gmt 0)

I'm trying to figure out the table design for my MySQL table for system users.

Currently I have 3 different sets of user: salespeople, leads, partners (sales people w/ different companies)

I will probably be adding more people to the mix soon such as: insurance reps, loan officers, etc.

Basic of how system users will interact:

Salespeople can have many leads and each lead belongs to only one salesperson.

Partners are global in that they are not assigned to specific salespeople and any salesperson can interact with any partner.

The other additions will probably be global as well..but I'm not quite sure yet. Anyways, would it be more efficient/logical to have one massive 'users' table to contain all the info for each category of users or to create a table for each and reference them that way?

One other note, is that the main three (salespeople, leads, partners) will all have a log in platform so the system will have to store and handle sessions and username/passwords of each...just in case that makes things any more complex..not sure.

Essentially this is a Customer Relationship Management (CRM) System. I've looked at the Table structures for a couple other systems including SugarCRM and it seems like they have separate tables for their main categories (but they dont have as many categories, I don't think)

 

brotherhood of LAN




msg:4505576
 6:34 pm on Oct 8, 2012 (gmt 0)

It sounds like you'd be better off with separate tables to store the different sets of user, as with one table you may end up with fields that are not relevant to the other sets of users. With separate tables you can design the table for the individual requirements of that user set.

The only table I'd definitely share between them is a credentials table that'd store userid,username,password,session and user type.

Ultimately if you feel most of your queries will be the same for all users then a single/couple table approach would be better, otherwise the multi-table approach may add a layer of complication, but also adds more flexibility.

tec4




msg:4505590
 7:36 pm on Oct 8, 2012 (gmt 0)

Oh okay, makes a lot of sense.

I'll do some brainstorming and see what I come up with. I think it might be beneficial to possibly group the salespeople and partners together, maybe (since they are all technically salespeople...just with different companies) then every distinct group can have their own table as well.

Thanks for the tips, too. The credentials table is a great idea!

tec4




msg:4506557
 4:39 pm on Oct 10, 2012 (gmt 0)

With the configuration table holding the user_id and user_type fields, is there a way I can have the row be deleted automatically if a particular user was removed from the system or would this have to be manual?

I've worked with foreign keys and such, but not sure how to do this if the type and id pertain to different tables (for example: agents, leads, partners).

What would be a good way to set that up structurally?

brotherhood of LAN




msg:4506580
 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.

tec4




msg:4506591
 6:50 pm on Oct 10, 2012 (gmt 0)

Wowzers - I have never done anything with stored procedures before, that's pretty sweet. I'm definitely going to give them a shot and look more into them :)

Will set this up and try it after work. Would the above work if the userid was the same for multiple users? For example, if the ID was auto-increment for each user table, there could potentially be 3 users with the ID of 1. In the delete call would there have to be a reference to the usertype?

Thanks for the help - your example is great!

brotherhood of LAN




msg:4506592
 6:56 pm on Oct 10, 2012 (gmt 0)

The userid is the primary key for all the tables I've put in the code above, no user will be able to have the same ID in the user credentials table as it has to be unique.

However there isn't anything stopping the user ID existing in more than one of the user type tables.

You may want to consider whether that's desirable or not (i.e. can a person belong to two different groups). If that's the case the procedure would need to be updated, and the usertype column would be better off being SET rather than ENUM.

Foreign keys are good for maintaining database integrity but if you design your code well enough then you don't really need them.

tec4




msg:4506631
 9:40 pm on Oct 10, 2012 (gmt 0)

Gotcha. Guess I'm still pretty new to some of these features, but it is starting to make sense.

I'll be sure to dig deeper into procedures later tonight. I really appreciate you spending the time to write all this out and help explain all this!

brotherhood of LAN




msg:4506687
 12:31 am on Oct 11, 2012 (gmt 0)

You're welcome.

Use of procedures is encouraged as it means less traffic between the client and the database, mainly that the CALL function is a lot more compact than sending queries that could end up being kilobytes in size. They're also quite strict about the variables you pass in and out of them so there's less scope for errors.

I've been using them more of late as they're useful for separating a lot of SQL out of your scripting language.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved