How to handle different user types by using groups?

Question

My question is in regards to having an application with two different user types, and each usertype has different "profile fields"(or details to look at it another way). To handle this, I used the "Group" structure to group the users together. I have the following table structure:

User: userid, username, password, groupid;

Group: groupid, detailsid, type, name

user_type1_details: id, other_fields

user_type2_details id, other_fields

Now, to select data from either user_type1_details or user_type2_details, you use the "type" field in the group table.

How do I select all the details data if the user is type 1 ?, How do I query the user_type_details table if the userid is 5, for example? What am I missing here? If the user is of type 1, I want to show all of the user_type1_details for that user, if it is type2, I want to show all of the user_type2_details. The details in each of these two tables are completely different.

user1026655 · Answer 1 · 2011-11-26T00:15:52.023

0

For user specific details, you could do this:

$userid = 5;
$tablelookup = array( 1 => "doctor_details", 2 => "user_details" );
$result = mysql_query( "select u.userid, u.username, u.password, g.detailsid, g.type from User u, Group g where u.userid=" . $userid . " and u.groupid=g.groupid", $db );
$data = mysql_fetch_row( $result );

Then feed the retieved values of data of that into

$tablename = $tablelookup[$data[4]];
$detailsresult = mysql_query( "select * from $tablename where id=" . $userid, $db );
$details = mysql_fetch_row( $detailsresult );

edited Nov 26 '11 at 00:15

answered Nov 25 '11 at 22:27

user1026655

78
6

do i need to add a userid column to the details tables? I want to return user specific details – MorganP Nov 25 '11 at 23:50
excellent, how do you maintain referential integrity in this case between the tables? – MorganP Nov 26 '11 at 00:02
re-read your answer, that is definately NOT my intent! haha - users can belong to ONE group only, and all of the users details are DIFFERENT. Can you show me how to rework my tables?? – MorganP Nov 26 '11 at 00:08
That's a tougher one. You might want to check out: [link](http://stackoverflow.com/questions/668921/foreign-key-refering-to-primary-keys-across-multiple-tables). I'm not sure if it's a 100% solution because the answer seems to imply that there's perfect alignment between the parent table and the child tables. Anyways, give it a read an hopefully it helps you over the last hurdle. :) – user1026655 Nov 26 '11 at 00:13
There, modified the answer to retrieve a user_details from the appropriate table depending on the type in the group. It's a little simpler this way. – user1026655 Nov 26 '11 at 00:17
so my table structure above is correct? just have to add the userid field to both the doctor_details table and the user_details table? – MorganP Nov 26 '11 at 00:21
good. Sorry for all the questions! New at this. Do you see another way of accomplishing this that is easier? Am I overcomplicating it? – MorganP Nov 26 '11 at 00:30

score 0 · Accepted Answer · answered Nov 26 '11 at 06:54

I would suggest a Schema along these lines:

create table Users
(
    userID int,
    userName nvarchar(100),
    userPassword nvarchar(100),
    groupID int
)

create table Groups
(
    groupID int,
    groupType nvarchar(100),
    groupName nvarchar(100)
)

create table Detail
(
    detailID int,
    detailName nvarchar(100)
)

create table GroupDetailMap
(
    groupID int,
    detailID int
)

create table UserDetailValue
(
    userDetailValueID int,
    userID int,
    detailID int,
    value nvarchar(100)
)

This will allow you to define any number of Groups and any number or kind of details that go along with those groups. You map groups to details using the GroupDetailMap table. Finally, you store the values for individual users in the UserDetailValue table.

When you want to create a new user, you would need to select a group first, and then get all the details that need to be added for that user:

select detailName
from Detail
inner join GroupDetailMap on GroupDetailMap.detailID = Detail.detailID
where GroupDetailMap.groupID = @groupID

Save all the details in the UserDetailValue table. Then, when you want to show the details for a user, just query the details for that user:

select Users.*, detailName, value
from Users
inner join UserDetailValue on UserDetailValue.userID = Users.userID
inner join Detail on Detail.detailID = UserDetailValue.detailID

If you have user details that are not alphanumeric, it can get a little hairier, but for the most part, this kind of schema is pretty flexible for storing varying properties associated with a parent record.

you could handle non alpha numeric data such as blob data, for example, an avatar, that is common to both in the user table? — MorganP, Nov 26 '11 at 19:09
An avatar seems like something that would be common to all users, and as such I would likely store it in the Users table, or a special Avatar table. In the past, however, when I've had multiple types (floats, integers, strings, etc) of properties for a parent record where *I've needed to preserve those types*, and can't get away with casting the a string, I've broken up the UserDetailValue type table into multiple tables like UserDetailValueString, UserDetailValueNumber, etc. Usually it's not necessary though, because you end up dumping it all on something like a webpage anyway. — Bert, Nov 26 '11 at 19:59
how is it possible to select multiple user details, when there are multiple user types posting comments? For example, displaying the username (or company name) depending on the group they are in, their comment, and a link to all of the user's comments if they are in the business group? The comments would be for a post in a forum. — MorganP, Nov 27 '11 at 00:30

How to handle different user types by using groups?

2 Answers2