Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2012
    Posts
    7

    database design schema etc

    I am trying to brainstorm a little database with tbl_Users, tbl_Company, tbl_Employees

    tbl_USERS - These are USER accounts that will be used to login to the website/db.
    tbl_COMPANY - These are numerous COMPANY fronts that will be view-able to one or more USERS
    tbl_EMPLOYEE - These are the employees that work for each COMPANY

    tbl_USERS
    - user_id (auto number)
    - user_name
    - Password
    - email

    tbl_COMPANY
    - company_id (auto number)
    - user_id
    - company_name
    - company_address
    - company_desc

    tbl_EMPLOYEE
    - emp_id (auto number)
    - company_id
    - emp_name

    This is what I need to happen in the application related to the tables/db
    - When the user logs in they will see images(URL links to other pages) reflecting each company. What I dont have above and what I need to concentrate on is the fact that depending on this user login I am going to give them access to one or more of these links.
    * so what I am thinking is that in the USERS table there would be a yes/no field for each COMPANY. I can test to see if the user is logged in, can test if the user has priviledges to link to that company with the yes/no value
    * but then this creates another issue. If I create a new company I would have to add a new fields onto the USERS table to allow the yes/no to be checked for each USER account.
    * I am looking to have an admin account that will allow the update of these yes/no fields.

    tbl_USERS
    - user_id (auto number)
    - user_name
    - Password
    - email
    - company1
    - company2
    - company3

    * I dont see any real issues with the tbl_employees as its a simply 1-many with the tbl_company
    * I dont see the need or how to connect the tbl_employees back to the tbl_users as they relate specifically to the tbl_COMPANY....I figure I can test to make sure the USER has permissions to view that COMPANY in the website and that should suffice.

    I think my main problem is surrounding the tbl_USERS and the tbl_COMPANY and how to add more users and more company's while maintaining some means in which to control what user can see what company's employees...in my case simply not allowing the URL link to happen, and once there add security incase the user tries to change the URL to verify is that logged in user (session user) has access to that tbl_COMPANY.

    Hope that makes sense....any thoughts would be greatly apprecaited.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    The simple answer is to create a new table to track a relationship between users and companies.

    USER_COMPANIES
    - user_id
    - company_id

    Make the primary key the combination of user and company.

    Add a row to this table for every company that a user is permitted to access.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Aug 2012
    Posts
    7
    Maybe if I create another table:

    tbl_User_company
    - user_id
    - company_id

    But then how to i update this? sort of confused on the design of it all and how it will flow to maintain the users and new companies added.

    EDIT: did see you post!!!
    Last edited by jaykappy; 07-17-13 at 12:56.

  4. #4
    Join Date
    Aug 2012
    Posts
    7
    So if I add that table...
    Then add a new company
    How do I write a query or allow the admin user to update the users that can view that specific company? That make sense?

    Trying to envision a form of some sort that would allow the admin account to update multiple users at once....or maybe this has to happen one at a time?

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    You can approach it from either direction, either a form that allows you to pick a user and then pick a list of companies that the user can choose, or present a form that allows you to pick a company then choose what users can work with that company.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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