Hello all...
I need help in forming the database design....
My Requirement is -
In a project, there are 3 master tables - User Master, Location Master, Company Master.
User Master-
| ID | UserName | Flag |
| 1 | Arti | 1 |
| 2 | Deesha | 1 |
| 3 | Amar | 1 |
Location Master
| ID | Location | Flag |
| 1 | Surat | 1 |
| 2 | Hydrabad | 1 |
| 3 | Delhi | 1 |
| 4 | Kolkata | 1 |
Company Master
| ID | Location | Flag |
| 1 | A | 1 |
| 2 | B | 1 |
| 3 | C | 1 |
| 4 | D | 1 |
I need to make a front-end facility such that -
Admin can select one user at a time - RadioButtonList and multiple Companies - CheckBoxList and click on "Submit" button.
Say - Admin selects "Arti" and companies - A,C,D.
Now, next time "Arti" user log in the system, she will see only 3 companies on her page - A,B,C.
Next, say Admin selects "Arti" and Location - Surat, Delhi, Kolkata and hits "Submit" button.
Next time, Arti should get only Surat, Delhi , Kolkata on her page.
Now , if admin gives right of Surat and Kolkata to Amar (other user), then Amar should have Surat and Kolkata on his page.
How to make database design for such requirement of assigning rights to users?
Is it a good practice to make other tables ? or add columns in same table ??
Please guide.....
Midhun TpPosted Oct 23, 2016, 9:42 PM
You can maintain this master tables as it is. The best thing is to create a new table for your requirements with columns userid, company id and location id.
Since here a user can have multiple locations, you may have to make location id and company id columns as varchar and insert id's of locations or companies separated by some comma.
Now while selecting back each user details, you can split it by commas and then join with location and company master.