Database Design Question

Posted on 2005-04-24
Last Modified: 2010-03-19

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:


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
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


Question by:Westside2004
    LVL 11

    Expert Comment

    i would emphasize the role-aspect a bit more to achieve this.

    add a new table

    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


    LVL 1

    Author Comment

    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?


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

    LVL 28

    Accepted Solution

    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 (
    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 (
    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.
    LVL 11

    Expert Comment

    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.



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    6 Experts available now in Live!

    Get 1:1 Help Now