Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2002
    Posts
    9

    Multiple Users Problem

    Hi all,

    i am designing the backend for a website that has several different sections and several different types of users.

    E.g

    General Users
    Suppliers
    Estate Agents
    Seller

    now i am having difficulty thinking of how i can create a system that would allow me to administer all these different types of user. My problem is, i have all these entities in seperate tables i.e.

    tblUser
    tblSuppliers
    tblEAgents
    tblSeller

    i had the thought of basically creating one user table that encapsulates all user details, and then have these link into the other sections, for example if could just add UserID as a foreign key to the employers table and thus have a defined link between the two. That way all users are in one place and i can administer roles and permissions centrally, my only concern is if this is a legitimate system to use, and i just want some other Database minds to have a look at it.

    Any Suggestions??

    Roberto

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Multiple Users Problem

    Yes, as your explanation says, Suppliers, Estate Agents and Sellers are just kinds of User and so should have their common data (including UserID) defined in a single table Users (not tblUser please! This is a database, not Visual Basic ) You may (or may not) still want separate tables Suppliers, EstateAgents and Sellers for the non-common information.

  3. #3
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    i had the thought of basically creating one user table that encapsulates all user details, and then have these link into the other sections, for example if could just add UserID as a foreign key to the employers table and thus have a defined link between the two. That way all users are in one place and i can administer roles and permissions centrally, my only concern is if this is a legitimate system to use, and i just want some other Database minds to have a look at it.
    Sounds good to me too!

    And here's a quickie page I did that explains the same concept:

    ASP Design Tips - Login Page
    http://www.bullschmidt.com/devtip-loginpage.asp
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  4. #4
    Join Date
    Jul 2002
    Posts
    9
    Thanks all,

    i have another question now. My inital plan of centralising the users into one table is the way i am going to go, however my new concern thinking of what may happen in the future (As i know it will change ) is the fact if i link the user id to the employer or landlords table i am then restricted to having to create a new record if they want a new user login(they may want to assign different roles to different users, admins, general users etc) so i need to think of a new solution. My thinking at the moment is such:

    User can belong to Zero(becuase its a general user table) or One Employer
    Employer can have One Or Many users

    Now my thought now turns to adding an EmployerID FK to the User table, however, i dont like that becuase there are other fields that will then have to be placed into the table. For Instance landLords has the same relationship with users as Employer, so all of a sudden i now have
    EmployerID and LandLordID as new columns in my table, obviuosly how i set it up in SQL Server will dictate how the relationships will be forced(or not as the case may be), but as i said i dont like this. I was thinking of creating a join table between Users and tables related, for instance:

    tblSiteUsers
    ID
    Etc...

    tblEmployers
    ID
    Etc..

    tblEmployerSiteUsers
    ID (Just to keep it Unique but probably not needed)
    EID (EmployerID)
    SUID (SiteUserID)

    The thing is with this, its not a many to many relationship so i dont think i really need it for structure, more for neatness on my part.

    does anyone have any views on this, otherwise it means implementing it and hoping for the best!! hahhahah! oh no!

    Any suggestions would be helpful!

    Rob
    Last edited by rm82; 02-25-04 at 11:03.

  5. #5
    Join Date
    Jul 2002
    Posts
    9
    Anyone??

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sounds good to me
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by rm82
    Anyone??
    Well I must admit I started getting lost in all that, which is why I didn't respond before. However, I suppose any response is better than none, so...

    Perhaps Employer and Landlord are 2 subtypes of a more general entity. e.g. Party? (Party is often used as the name for an entity that may represent a Person or an Organisation in different cases - e.g. an Employer could be Microsoft or it could be Mr John Smith).

    So now the relationships between User and Employer and between User and Landlord become 2 relationships between User and Party:

    create table party( party_id ... primary key, party_type ... );
    create table employer( party_id references party primary key, ... );
    create table landlord( party_id references party primary key, ... );
    create table user_relationship( user_id, party_id, ... );

  8. #8
    Join Date
    Jul 2002
    Posts
    9
    Hi there,

    Thanks for the responses!! i have concluded on a structure now, and trust it not to be the one i discussed earlier. The way i am going to do it this.

    1) Create a central Users table that will take in all the user information, name, address, Uname, Pword etc.

    2) Create seperate tables that deal with Employers and LandLords etc to take in their personal details.

    3) Link them by adding EmployerID and LandlordID into the User table

    Now i didnt really want to do this, however, the more and more i racked my brain i kept comming up to the same conclusion, and that is that all relationships between user and say Employers and Landlords is one to many! i cant invent a many to many.

    Now i do have a centralised table and i can link users to either a employer or a landlord. My problem is i now create an extra <NULL> value if that person is not a memeber of say employers or Landlords, and two <NULL> values if that user is just a general site user.

    This site is just huge so the db is complicated, i am having to rework areas of my initial design, which is something i didnt want to do.

    One last time, can i legally do this, or am i just asking for trouble? I apologise for all the questions, i just want to get this right!

    Rob
    Last edited by rm82; 02-27-04 at 12:35.

  9. #9
    Join Date
    Mar 2004
    Posts
    28
    Have a "People" table. Put EVERYONE in it.

    Have a "Role" table. Owner, Landlord, Tenant.

    Have a "Property" table. Put each property into it. An attribute for property is perhaps "for sale".

    Have an Xref table.

    Register a person into the Xref table. Put their peopleID, the Role they play and for which property. Put a start and end date and whatever other attributes needed.

    Given this model, a person could be potentially a tenant AND a landlord.

    Make the three keys unique - a person can't be a tenant twice. If it is apartments where one person CAN be a tenant twice then put an extra column in the relationship table and call it "address" or "room number" or something.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •