best practice: db schema and storing data

[DaveC426913]

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?

 

[mrdenny]

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

 

[DaveC426913]

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

 

[mrdenny]

no problem.

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

 

[ceh4702]

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.

 

[DaveC426913]

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.