db schema

[Jaykappy]

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.

 

[Jaykappy]

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.

 

[Jaykappy]

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?

 

[Pat Hartman]

You've come around to the right idea. You need the new table. Cntl-Click both User_id and company_id to select them both and then click the big key in the ribbon to make a compound primary key.

You don't do this with an action query. You'll need a form with a subform. Which is which is dependent on how you want to work with the data. The tbl_user can be used as the mainform and the tbl_User_company can be used as the subform. Or you can use tbl_company as the main form and tbl_User_company as the subform. Depending on which "field" is used in the main form, the "other" is what will be visible in the subform.

 

[Jaykappy]

You've come around to the right idea. You need the new table. Cntl-Click both User_id and company_id to select them both and then click the big key in the ribbon to make a compound primary key.

You don't do this with an action query. You'll need a form with a subform. Which is which is dependent on how you want to work with the data. The tbl_user can be used as the mainform and the tbl_User_company can be used as the subform. Or you can use tbl_company as the main form and tbl_User_company as the subform. Depending on which "field" is used in the main form, the "other" is what will be visible in the subform.

I assume that you are referring to this from an Access point of view? I am interacting with this data via a web page using PHP so going to be a bit different...just a little confused on the form and sub form...can you explain this...will help me better understand what you are referring too.
Appreciated...cheers

 

[Pat Hartman]

This is an Access forum but you are not using Access so you would probably get more relevant help at a PHP site. Don't confuse Jet/ACE with Access. They are the database engines that Access is closely linked to but they are stand alone products. If they weren't, you couldn't use them from PHP. So whether your database is Oracle, MySQL, SQL Server or Jet/ACE, your process is the same. You just need slightly different SQL and ODBC connection strings but the "page" stuff would be the same. If I knew anything about PHP, I would offer a suggestion but I don't so I won't