Results 1 to 6 of 6
  1. #1
    Join Date
    May 2011

    Designing advice - Single user to multiple companies

    I'm working on developing a web application, the main purpose for that web application is to allow users to logon, download and respond to daily reports. The database is designed to have each user to be linked to only one company, and now I have been requested to modify it so each user can have access to multiple companies and respond on their behalf.

    For example: User John has access to company A and B. Currently my design only works if he has access to either A or B. Any suggestions how to modify my database to make that change possible ?

    Users Table:
    • UID (PK)
    • UserName
    • CompanyID (FK)
    • FName
    • LName

    Companies Table:
    • CompanyID (PK)
    • Name
    • StartDate

    Am I required to create a cross table?
    For now the only solution in my mind is to have multiple user id's with same UserName and then deal with that issue from the servlet sides, but I don't think this is a good to go with..
    Thank you

  2. #2
    Join Date
    Feb 2004
    New Zealand
    why just create a CompanyUser Table

    CompanyUserID (PK)
    UID (FK)
    CompanyID (FK)
    hope this help

    See clear as mud

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008

  3. #3
    Join Date
    May 2011
    Thank you, but I can not see the benefit from having a 3rd table with the ID's and creating a 3rd ID ? Can you please explain a little bit?!

  4. #4
    Join Date
    Nov 2004
    out on a limb
    How else do you think you can associate one user with more than one company, or one company with more than one user. Its called an intersection table. But i would suggest the PK should be a composite of user and company (those columns are a PK in userscompanies AND a FK to tgeir respective 'parent' tables. But then again id also call tables by plurals
    Eg users, companies abd userscompanies..
    Irrespective of the detail the third table is the only way i can see of modelling this specific requirement, a many to many relationship

    Push company id from users into the intersection table, along with anything else pertinent to that intersection. Ferinstance you could have a valid from / to date. Ie user Wael can see company xyz data from jan 2016 but company abc upto dec 2015.....
    Last edited by healdem; 02-23-16 at 03:13.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2016

    This is what I had in mind

    Put this together before seeing the prior responses to your question ...
    Attached Thumbnails Attached Thumbnails LoginWithMultipleCompanies.JPG  

  6. #6
    Join Date
    May 2016

    For modeling correctly a db (See example on this dbforums post

    1. Build a conceptual data model (CDM). The Logical data model is very simple then to generate. With tools, the LDM is automatically built as well as the SQL script for creating the DB
    2. Use dedicated tools which build CDM not only LDM like :
      1. Power*AMC (excellent but expensive)
      2. JMERISE, excellent freeware here

    And of course apply at least the 3 first Normal forms (NF).

Tags for this Thread

Posting Permissions

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