• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

Database Design Question

Hi,

I wanted to see if someone could suggest a good solution to the problem I am having.

The goal is to store menu settings in my database so a user or myself can turn off certain "windows" and links within those windows off and on.

So say we have three user types.

UserTypeA, UserTypeB, UserTypeC

In our app we have a right navigation menu made up of content windows basically boxes with sublinks titled:

Reports
-Report1
-Report2

Users & Groups
-Add User
-Delete User

Based on a users role/permission we first need to decide which window's they see (Reports, Users & Groups, etc) then within each "content window" there may be a given set of sublinks. So two users may have "Users & Groups" window available, but one user might see "add user" and "delete user" while user # 2 would just see "add User".

I need to have the ability to store these settings in the database. We have a "Preferences" section in our app and I want to allow the user to check boxes that determine what they want to show or dont show. This way a user checks or unchecks some boxes and then hits save and its stored into the database, so next time they login I can pull this info from the db and display the menu as they want it..

So to start I created a table called MenuOptions with a structure like:
-----
MenuOptionID INT PK
UserID INT FK
Reports BIT
UsersGroups BIT
etc, etc
----

I am not sure how to handle the sublink part in my database. The "Reports" option in my table can have 3-4 sublinks that I display in the UI, I need to somehow relate that option in the above table to a set of sub-options if that makes sense.

Any help appreciated

Thanks

-ws
0
Westside2004
Asked:
Westside2004
  • 2
1 Solution
 
lluthienCommented:
i would emphasize the role-aspect a bit more to achieve this.

add a new table
---Role:---
RoleID
RoleName
------------

all the links you add to either of the windows will then have an extra field "role"
so, for instance you have a role "Users & Groups view" and a role "Users & Groups edit"

in the preferences screen you connect these roles to a user via a checkbox.
so you have a many-to-many relationship between the new "role" table
and a one-to-many relationship between the sublink and the "role" table.

this solution means you store the links in the database.
i don't know if this is the case, or if this is a problem.
there are ways around this, but those depend on the environment your app runs in

cheers


0
 
Westside2004Author Commented:
Thanks for the reply.

So storing links in the db is basically just html.  What datatype is usually used for storing links/html in a db?

Text?

Is it a bad thing to store html in the db?

thx
0
 
rafranciscoCommented:
Here's how I would do it.  First I would create a table for the different menu options, with a built-in hierarchical structure:

CREATE TABLE MenuOptions (
MenuOptionID INT NOT NULL PRIMARY KEY,
Name  VARCHAR(50),
ParentMenuOptionID INT FOREIGN KEY REFERENCES MenuOptions(MenuOptionID),
Link VARCHAR(100)
)

If the ParentMenuOptionID is null, then it is the parent.  Example data for this table will be:

MenuOptionID    Name                  ParentMenuOptionID
1                      Reports                NULL
2                      Report1                1
3                      Report2                1
4                      Users & Groups    NULL
5                      Add User             4
6                      Delete User          4

Then I would create a second table that will indicate if a user has access to a particular menu option.

CREATE TABLE UserMenuOptions (
UserID  INT FOREIGN KEY REFERENCES User(ID),
MenuOptionID INT FOREIGN KEY REFERENCES MenuOptions(MenuOptionID),
Access BIT)

You will define in this table if a particular user has access to the particular Menu Option.  A value of 1 in the Access column means that the UserID has access to the MenuOptionID.

Hope this helps.
0
 
lluthienCommented:
generally, i think storing a html in a db is not a brilliant idea.
i was assuming before that you had some means to store the links in the db allready.

seeing as you have not, and from your reply i seem to read that your database is webbased.
i would use these four steps.

1> retrieve all the roles the currentyly logged in user has. (plain text role_add_data_client, role_delete_data_client etc)
2> i would then put all these roles into a clientside script - array.

3> then, for each link that you want to secure, like:
<a href='http://mypage/addclient.asp'>add client</a>
you can add a property to your html, like so.
<a role_add_data_client href='http://mypage/addclient.asp'>add client</a>

4> upon loading the page you just run through all the links,
setting all of those you are not priviliged to, to hidden.

that way you _can_ set priviliges globally, without the fuss of storing each and every link into the db.
that's just my solution to this and it kind of depends how fluent you are with different languages and
what your environment looks like exactly.

cheers

0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now