×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

best practice: db schema and storing data

best practice: db schema and storing data

best practice: db schema and storing data

(OP)
I'm designing a database that manages user access to an education application that contains courses and modules.

A user might have access to x out of y courses, and as well as access to v modules out of w modules inside each course. So, if I'm managing 5 course with 6 modules in each, a worst case scenario would store 30 access rights settings for each user, meaning 30 rows in the table of users vs. courses and modules.

(Additionally, to complicate things, a user might be a member of one or more groups, which also can have the same kinds of access, but that doesn't directly bear on my question.)

My question is this:

Is it good database design to have a table that contains one row for each and every access right to a course and/or module for each user? Even when the data needed is simply binary (permission granted/denied)?

I have seen a method used where lots of binary data can be stored compactly by using a bit mask - requiring only one row per user.

eg. A user with access rights to modules 1,2,4 and 5 will have their value stored as 11011 (stored as integer 27). This means a small and fixed number of rows per user. (The downside of course, is that it places an upper limit on the total number that can be stored, based on what sized integer you use).

Advice?

RE: best practice: db schema and storing data

Using bitwise operators (needed for breaking appart the 27 into 1, 2, 4 and 5) are some of the most CPU intensitive operations that you can do on the database (this is based mostly on SQL Server).

If I was setting this up I would simply store one record for each module the user has rights to, and no record for modules they don't have access to.

In the table that grants them rights to the course I would have a flag that grants them rights to all modules within that course there for by passing the need to store the indivudial module rights for each module for a course they get rights to all modules on.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible.  All it takes is a little research. (Me)

http://www.mrdenny.com
Donate to Katrina relief

RE: best practice: db schema and storing data

(OP)
"...Using bitwise operators ... are some of the most CPU intensitive operations that you can do..."

OK, that answers my question (not that speed & resource mgmt is an issue on this scale, but scalability is a major factor in best practrices, right?)


"store one record for each module the user has rights to, and no record for modules they don't have access to."

Yeah, my idea too.  That's what I meant my "worst case" in terms of rows needed.


"...In the table that grants them rights to the course I would have a flag that grants them rights to all modules within that course..."

Good idea.


Thanks much.

RE: best practice: db schema and storing data

What if someone is not there one day and you want to access the information?  I dont see what is so secretive about a course.  You might want to limit access to grades and payroll or student  payments or address information.  A lot of that might be considered public information if it is a public school or a non-private school.

If you do not like my post feel free to point out your opinion or my errors.

RE: best practice: db schema and storing data

(OP)
It's a service as much as a course. This particular one teaches people how to financially plan certain life events, and has a proprietary calculator tool that shows their financial potential & such.

Giving them access to everything would
a] present them with information that is potentially not relevant to them, diluting the goal of a tight learning experience
b] give them access to elements of the course that they have not paid for (you have to buy in to the plan, like a membership).

Actually, there is no real personal user information such as   addresses, etc.. All the data revolves around worksheets for hypothetical income and expense events and multiple scenarios.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close