Connecting Tech Pros Worldwide Help | Site Map

Databases : Membership roles - designing tables

Newbie
 
Join Date: Sep 2007
Posts: 5
#1: Sep 5 '07
Hi there,

I am a newbie to web development and db programming. I have recently started reading about ASP.net and SQL. To gain some practical experience i am trying to do the following :
1) design a system that will assign courses and professors to classrooms each term;
2) assign students to those courses based on priorities given to seniors and those in major tracks;
3) publish class rosters for each classroom and a report showing which professors will be assigned where.
4) Information produced by this system should be accessible on line.

I have finished designing a website in ASP.net and C# where users can log on to website and depending on their role(i.e. students, faculty, or admin) they are able to access certain pages specific to their role.

In the next step is where i have questions.
Q 1: I created a DB using SQL. Then i created a table each for courses, professors, and classrooms. I think i need to create a fourth table here to be able to assign courses and professors to each classroom, right?
What is the best way to accomplish this task?

Q 2: Say i have a table with 3 rows(courseID, profID,classID), can i assign different foreign_key_relationship on each row? (in other words can i have the courseID row have a foreign_key_relationship with the primary key courseID in a different table, and profID have a foreign_key_relationship with the primary key profID in a different table, and classID have a foreign_key_relationship with the primary key classID in a different table.) ?

Q 3: My website that i created makes use of membership and roles. How can i link the users who can log on to the website to access their specific records and not another student's record. Do i have to play around the ASP.net memberships tables and try to add a new column for the studentID? ? I am not clear how to let studentA access studentA's records and not any other student record.

Q 4: What would be the best way to design my tables how should they be related?

Any suggestions? or ideas on how to best do this? I can really use all the help i can get at this point. I really appreciate your time. Thanks
Mazin
bartonc's Avatar
Moderator
 
Join Date: Sep 2006
Location: Minden, Nevada, USA
Posts: 6,400
#2: Sep 5 '07

re: Databases : Membership roles - designing tables


There is an interesting article on normalization here, in case you are really taking your first steps in DB design.
Newbie
 
Join Date: Sep 2007
Posts: 5
#3: Sep 5 '07

re: Databases : Membership roles - designing tables


Quote:

Originally Posted by bartonc

There is an interesting article on normalization here, in case you are really taking your first steps in DB design.

hey thanks for ur help. The article was very informative, however it didn't specifically answer Q 3. Any ideas about what i should be looking to do here?

Thanks
kakas
pbmods's Avatar
Site Moderator
 
Join Date: Apr 2007
Location: Texas
Posts: 5,435
#4: Sep 5 '07

re: Databases : Membership roles - designing tables


Heya, Kakas.

Not sure how you'd do it in ASP, but in PHP, the solution would be to include code at the top of each page that checks the User's roles against the expected ones. If they match, the page is served. If they don't, the User is redirected to a login page.

Note that this is all done manually; there is no built-in access system for PHP.

My recommendation for a simple membership roles system would be to create four tables:
  • Namespaces:
    • Each page defines what namespace it is a part of.
    • Each page should only have one namespace.
    • In the namespace table, you record each unique namespace, as well as its default permissions (for example, the 'home' namespace might allow access by default, but the 'member' namespace would deny access by default).
    • I call them 'namespaces' because I also use this association for sharing certain data in between pages and for tweaking templating behavior. You can call them whatever you want.
  • Groups:
    • In the groups table, define each unique group, as well as its 'override' default permission (for example, the 'admins' group would be able to access any namespace by default, regardless of what the namespace's default is).
  • User/Group Map:
    • This one's up to you in terms of how you want to implement it. You might want to create a separate table so that you can have a many-to-many relationship between Users and groups, or you might want to simply add a 'group' field to your Users table.
  • Group Permissions:
    • And here is where the magic happens.
    • In this table, you set up rules that govern access. For example, you might create a 'deny' rule for the 'members' group to the 'admin' namespace [so that normal members could not access administration modules].

The next step would be to then implement the code to process this data.
  • At the top of each page, set the page namespace (for example, in PHP, you could use the define() method).
    • NB you could alternatively set up this relationship in a separate table in the database, but I've found that it's much more convenient to just include the code in the page itself, which is perfectly secure unless a User can edit files on your server, but this permissions setup would be useless in that event anyway.
  • Load the User/Group map and determine which groups the User is a member of (if any). If the User is not logged in, you can skip this step.
    • Load the permissions rules for the namespace and check to see if any of them match the User's group(s). If you have a match, then that will determine the User's access.
    • If there are no matching group rules, use the User's group default.
  • If the User is not logged in or is not a member of any group, use the namespace default.

Here are some examples. Assume the following database structure:
  • Users table (with many-to-one group mapping):
    • User 1: 'members' group (gid 1)
    • User 2: 'admins' group (gid 2)
  • Groups table:
    • Group 1: 'members', default permission is 'allow'
    • Group 2: 'admins', default permission is 'allow'
  • Namespaces table:
    • Namespace 1: 'home', default permission is 'allow'
    • Namespace 2: 'member', default permission is 'deny'
    • Namespace 3: 'admin', default permission is 'deny'
  • Permissions table:
    • Rule 1: namespace 'admin' (nsid 3), group 'members' (gid 1), rule 'deny'

Ok. Example 1:
The User is not logged in, and he tries to access the homepage. In the homepage, the 'home' namespace is defined.

Since the User is not logged in, we don't bother checking his group; we'll just use the default permission for the namespace, which is 'allow'.

User is granted access.
-----

Example 2:
The User is still not logged in, and bolstered by his latest success, tries to go to the members-only area ('member' namespace).

Since the User is not logged in, we don't bother checking his group; we'll just use the default permission for the namespace, which is 'deny'.

User is denied access.
-----

Example 3:
User #1 ('members' group) is logged in. He tries to go to the admin control panel ('admin' namespace).

Since the User is logged in, we check his group rules. He matches Rule #1 (deny access to the 'members' group for the 'admin' namespace). Permissions rules overrule all other rules and defaults.

User is denied access.
-----

Example 4:
User #2 ('admins' group) is logged in. He tries to go to the admin control panel ('admin' namespace).

Since the User is logged in, we check his group rules. Since there are no rules for the 'admin' namespace that match the 'admins' group, we drop down to using group defaults.

The default rule for the 'admins' group is 'allow'.

User is granted access.
-----

Hope this helps.

If you have any questions or comments, let's have 'em! :)
Lives Here
 
Join Date: Sep 2006
Posts: 12,070
#5: Sep 5 '07

re: Databases : Membership roles - designing tables


I'd really like to comment but that post is simply too long for me to read it right now.


P.S Did you just buy a new keyboard?
Newbie
 
Join Date: Sep 2007
Posts: 5
#6: Sep 6 '07

re: Databases : Membership roles - designing tables


Thanks for your help pbmods. I am working on it right now and it looks good so far.
I will make sure i come back when i have any more questions. Appreciate it.
kakas
pbmods's Avatar
Site Moderator
 
Join Date: Apr 2007
Location: Texas
Posts: 5,435
#7: Sep 6 '07

re: Databases : Membership roles - designing tables


Heya, Kakas.

Good luck with your project, and if you ever need anything, post back anytime :)
Reply