design questions - tables to support users

[Kimmmmy]

I am running into a problem and need another set of eyes. I am in the middle of building a database to support logins to my website (username & password) There will be different roles with different persmissions. Certain roles will have corresponding content in other tables. For example I have a buyer table, I also have a buyer role. Then I have a memberrep table that contains a record for anyone who can login to the system. One table for all logins - no mater the role. I need to connect a single buyer to a single login in the memberrep table. I will have the same issue with the sellers and the salesreps. Each will have a one to one relation to a record in the memberep table. Am I better off linking directly to the table without the 3rd table? Or can I simply add the 3 or 4 columns into the memberrep table like buyerID, sellerID, salesrepID, ect. And then only populate the ID of the login that relates to the external table? I am at a loss here.. Is there another way to make this work? Here is a screen shot of what I have already. Any issues jump right out? http://i654.photobucket.com/albums/uu269/squeakita/dbs/gpMemberstables.jpg[^] Any suggestions?

[Luc Pattyn]

Hi, I'm not a DB expert, but your drawing looks way too complex. I see a lot of duplicate field names, which to me is typical for a poor design. For starters, I would have a PERSON table, holding a person ID, and all info regarding that person, independent of his role(s); so fname, lname, phone, email, etc. Then, I would have a PERMISSIONS table, holding an role ID, a role name, and all the permissions; not sure whether I would choose many permission columns, or use several rows with only one permission field (and when in doubt several rows/1 column often is the right choice). And finally I would have a ROLES table, holding a person ID and a role ID. If a person has multiple roles, just give him multiple rows. As a result, in order to determine whether person X has permission Y, you would search person X and permission Y in a join of all three tables. If you find one or more rows that match, the permission is granted. Hope this helps.

Luc Pattyn

:badger: :jig: :badger:

Have a look at my entry for the lean-and-mean competition; please provide comments, feedback, discussion, and don’t forget to vote for it! Thank you.

:jig: :badger: :jig:

[Kimmmmy]

Thanks for the reply. I am confusing myself - believe me. I have a working user/role table setup that is here - the memberrep table is your person table. I've used this table design for many websites. [^] Independent of the login tables -(and this is where I get confused explaining what has to happen) I have several other 'data' tables. A buyer , who will be a buyer and have a login. I want to relate the buyer to his user login. I want to do the same with a seller (another similar but different table) and yet a 3rd, salesrep. In order for my buyer to login to the admin and see his data in the buyer table, I need to relate his buyer record to his login record. I am considering adding the memberRepID to the buyer table - and then I will have the relation. But duplicate data (fname,lname,email,etc) It's like it's calling me to create seperate login tables for each external 'content' type, The buyer, seller and salesrep tables are not tables I am using to define the role. does that make sense?

[Luc Pattyn]

Kimmmmy wrote:

does that make sense?

I couldn't tell, you've lost me early on. I suggest you start from the hard data, that to me is PERSONS. And I really would insist on a very neutral name for that table, it has nothing to do with functions, roles, permissions, which are all weakly defined and volatile (they can evolve in all kinds of ways), non-unique, and overlapping. IMO in general, more tables is wrong except for normalization (i.e. avoiding duplicate entries in fields). Think of it like this: using more tables will result in needing more code, and does it really improve the quality or the power of your system? Here is a functional question: does a person with two roles also use two logins? if yes (I hope not), the login belongs somewhat to the role; if not, the login belongs to the person, and may or may not be one or a few fields in the PERSONS table; I would be inclined to keep it apart, just personID and login data. I wish an expert kicked in here. I would learn too. :)

Luc Pattyn

:badger: :jig: :badger:

Have a look at my entry for the lean-and-mean competition; please provide comments, feedback, discussion, and don’t forget to vote for it! Thank you.

:jig: :badger: :jig:

[Kimmmmy]

Yes, a person with two roles would have one login. Although in this site, I do not see that happening. (famous last words) :) I appreciate your help. I need to do some more research. Thank you for your time.

[Mycroft Holmes]

Didn't look at the graphic but this is a simple issue, you need to answer 1 question. Can a user have more that 1 role. Yes UserTable - all the details of the user as per Luc's suggestion RoleTable - a list of all the roles available LinkTable - a many to many link table with userid and roleid When the user logs in they select the role to be used, you have a chicken/egg issue here - how do I filter the possible roles bfore the user has logged in, you don't you validate the role after the user and return for a role selection is an incorrect one is requested. No UserTable - with the additional field of RoleID RoleTable - a list of all the roles available Validate the user only, role is automatic.

[Luc Pattyn]

Seems we fully agree, your description is much better though. You had the advantage of not having seen the diagram! :)

Luc Pattyn

:badger: :jig: :badger:

Have a look at my entry for the lean-and-mean competition; please provide comments, feedback, discussion, and don’t forget to vote for it! Thank you.

:jig: :badger: :jig:

[Mycroft Holmes]

So for once I can thank my net nazi for not allowing image sharing sites :-D