DB design - schema
Posted by: Jay Kappy
Date: July 17, 2013 09:40AM

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.

Options: ReplyQuote


Subject
Written By
Posted
DB design - schema
July 17, 2013 09:40AM
July 17, 2013 09:52AM
July 17, 2013 09:55AM
July 20, 2013 09:30PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.