• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Liutauras Vilda
  • Knute Snortum
  • Bear Bibeault
Sheriffs:
  • Devaka Cooray
  • Jeanne Boyarsky
  • Junilu Lacar
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
  • salvin francis
Bartenders:
  • Tim Holloway
  • Piet Souris
  • Frits Walraven

How to design or define this tables?

 
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Below are the current DB table design

user table has user details
role table has role details (ex: admin, manager, supervisor)
permission table has permission details (ex: sales_order, purchase_order)
access table has 3 rows - read, update, write
role association table has role_id, per_id, access_id
role child association 1 has user_id, role_id
role child association 2 has user_id, role_id, country_id
currently the access is based on role level….

how to design the DB to access specific level instead of role level.

ex:

admin -> manager -> sales_order -> read
admin -> manager -> purchase_order -> read and write.

please suggest how to design the tables so that we can achieve the expecation.

Thanks,
 
Saloon Keeper
Posts: 11471
247
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What do you mean by access specific level?
 
Johnny Joseph
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
read, update, delete
 
Stephan van Hulst
Saloon Keeper
Posts: 11471
247
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So you mean, given the tables that you described above, how to write a query that returns the permitted access type for a specified user and permission?

For example, given the user "admin" and the permission "purchase_order", you want it to return "read" and "write". Is that correct?

First show us the query you've tried to write yourself. Then tell us what you're having problems with.
 
I wish to win the lottery. I wish for a lovely piece of pie. And I wish for a tiny ad:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!