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.

Answer 1

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.

Answer 2

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 );