My real question is when it comes to getting a credit card applicatoin with two different user types, and every usertype has different "profile fields"(or particulars to check out it one other way). Additional, I made use of the "Group" structure to group the customers together. I've the next table structure:
User: userid, username, password, groupid
Group: groupid, detailsid, type, title
user_type1_particulars: id, other_fields
user_type2_particulars id, other_fields
Now, to choose data from either user_type1_particulars or user_type2_particulars, you apply the "type" area within the group table.
How do you choose all of the particulars data when the user is type 1 ?, How do you query the consumer_type_particulars table when the userid is 5, for instance? What shall we be held missing here? When the user is of type 1, I wish to show all the user_type1_particulars for your user, if it's type2, I wish to show all the user_type2_particulars. The particulars in all these two tables are different.
For user specific particulars, you could do this 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 information of this into
$tablename = $tablelookup[$data]; $detailsresult = mysql_query( "select * from $tablename where id=" . $userid, $db ); $details = mysql_fetch_row( $detailsresult );
I recommend a Schema along wrinkles:
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 help you to define a variety of Groups and then any number or type of particulars that accompany individuals groups. You map groups to particulars while using GroupDetailMap table. Finally, you keep values for individual customers within the UserDetailValue table.
When you wish to produce a new user, you should choose an organization first, after which get all of the particulars that should be added for your user:
select detailName from Detail inner join GroupDetailMap on GroupDetailMap.detailID = Detail.detailID where GroupDetailMap.groupID = @groupID
Save all of the particulars within the UserDetailValue table. Then, when you wish to exhibit the particulars for any user, just query the particulars for your user:
select Users.*, detailName, value from Users inner join UserDetailValue on UserDetailValue.userID = Users.userID inner join Detail on Detail.detailID = UserDetailValue.detailID
For those who have user particulars that aren't alphanumeric, it may obtain a little hairier, but typically, this type of schema is fairly flexible for storing different qualities connected having a parent record.