User Group Membership
-
More of a theory question than anything. I have a website where people who register fall under two different user types, say attorney and client for example. If a person registers as a lawyer, they fill out a lawyer profile. If they register as a client, they fill out a client profile. The two profiles are different. So I am trying to figure this out from a database standpoint. Do you think the best bet is a three table setup? User ------ ID Email Password User_Type (1 for lawyer, 2 for client) Lawyer ------- ID (AI field) User_ID First Last etc Client -------- ID (AI field) User_id First Last Violation etc... Or I could do a two table with just Lawyer and Client and check both tables at login time? It would be more code intensive but less data. What is a good approach here? Open to suggestions. Cheers, --EA
Turn it on it's ear, create a person table and attach an attribute for role, use a many to many link table to cater for a lawyer who is also a client! Person PersonID lnkPersonRole LinkID PersonID RoleID Role RoleID
Never underestimate the power of human stupidity RAH
-
Turn it on it's ear, create a person table and attach an attribute for role, use a many to many link table to cater for a lawyer who is also a client! Person PersonID lnkPersonRole LinkID PersonID RoleID Role RoleID
Never underestimate the power of human stupidity RAH
That is a good solution, thank you. In regards to the profile aspect, how would you handle role specific attributes? Say, I want to store a lawyer's license number, or a client's height and weight?
-
That is a good solution, thank you. In regards to the profile aspect, how would you handle role specific attributes? Say, I want to store a lawyer's license number, or a client's height and weight?
You have 2 real choices, add each attribute as a column in the person table (accepting that a lot will be blank) or add another table of person attributes and only link the valid attributes to each person record. AttrType AttrTypeID Attr [Licence No] PersonAttr AttrID AttrTypeID AttrValue [123456A] The attributed solution is technically the most correct method, it is also the most complex and that complexity can cause issues. The column method means that every time you need to add an attribute you have to touch the code. It then becomes a business decision which way to go.
Never underestimate the power of human stupidity RAH
-
You have 2 real choices, add each attribute as a column in the person table (accepting that a lot will be blank) or add another table of person attributes and only link the valid attributes to each person record. AttrType AttrTypeID Attr [Licence No] PersonAttr AttrID AttrTypeID AttrValue [123456A] The attributed solution is technically the most correct method, it is also the most complex and that complexity can cause issues. The column method means that every time you need to add an attribute you have to touch the code. It then becomes a business decision which way to go.
Never underestimate the power of human stupidity RAH
Seems like another good answer. What is the purpose of the AttrId column in the PersonAttr table? A simple primary key? Or is it foreign key for another table?
-
Seems like another good answer. What is the purpose of the AttrId column in the PersonAttr table? A simple primary key? Or is it foreign key for another table?
EVERY table needs a primary key (in an OLTP database) so the AttrID is an IDENTITY field and is the primary key. Note the format (int) is a convention, some like GUIDs but I can't stand them. I also insist the primary key field is the FIRST field added to the table and is the table name + 'ID' so it should have been 'PersonAttrID' When you have 100s of table you do not want to have to chasing down the name of the PK field for each.
Never underestimate the power of human stupidity RAH
-
EVERY table needs a primary key (in an OLTP database) so the AttrID is an IDENTITY field and is the primary key. Note the format (int) is a convention, some like GUIDs but I can't stand them. I also insist the primary key field is the FIRST field added to the table and is the table name + 'ID' so it should have been 'PersonAttrID' When you have 100s of table you do not want to have to chasing down the name of the PK field for each.
Never underestimate the power of human stupidity RAH
I am with you on the guid issue, unnecessarily complicated. I see no reason to go further than an identity field. This project is MySql and CakePHP, so it dictates it's own naming convention for tables and columns and such. Well, you can get around it but it requires unnecessary code to get around. Anyways, thank you for the guidance I am going to go with person table, attribute table and then third table that has id, person_id, attr_id, and attr_value. Is this really a true many to many relationship, adding the attr_value column to the joining table?
-
EVERY table needs a primary key (in an OLTP database) so the AttrID is an IDENTITY field and is the primary key. Note the format (int) is a convention, some like GUIDs but I can't stand them. I also insist the primary key field is the FIRST field added to the table and is the table name + 'ID' so it should have been 'PersonAttrID' When you have 100s of table you do not want to have to chasing down the name of the PK field for each.
Never underestimate the power of human stupidity RAH
One more question about this: for fields that are common to all role types and mandatory, would you encourage adding those to the person table? For example, email address, age?
-
EVERY table needs a primary key (in an OLTP database) so the AttrID is an IDENTITY field and is the primary key. Note the format (int) is a convention, some like GUIDs but I can't stand them. I also insist the primary key field is the FIRST field added to the table and is the table name + 'ID' so it should have been 'PersonAttrID' When you have 100s of table you do not want to have to chasing down the name of the PK field for each.
Never underestimate the power of human stupidity RAH
-
One more question about this: for fields that are common to all role types and mandatory, would you encourage adding those to the person table? For example, email address, age?
Normally yes, at least for data that's directly connected to the person, such as age, weight and length and such. But while an email address might be personal, it might also be connected to the work role, and a person might also have more than one email address. And so on. It depends on the purpose of your application, and how it might change in the future. Always plan ahead. <edit>fixed typo</edit>
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
More of a theory question than anything. I have a website where people who register fall under two different user types, say attorney and client for example. If a person registers as a lawyer, they fill out a lawyer profile. If they register as a client, they fill out a client profile. The two profiles are different. So I am trying to figure this out from a database standpoint. Do you think the best bet is a three table setup? User ------ ID Email Password User_Type (1 for lawyer, 2 for client) Lawyer ------- ID (AI field) User_ID First Last etc Client -------- ID (AI field) User_id First Last Violation etc... Or I could do a two table with just Lawyer and Client and check both tables at login time? It would be more code intensive but less data. What is a good approach here? Open to suggestions. Cheers, --EA
I would use you solution but with a slight change. I would include all columns common to Client and lawyer in the user class. i.e First, Last. If there are few columns that are different for Client and Lawyer, I would just role everything up to User, and allow nulls for those columns which don't apply to both client and lawyer. You will need some business rules to determine when those null fields should have a value.