homepage Welcome to WebmasterWorld Guest from 54.205.144.54
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
Forum Library, Charter, Moderators: coopster & jatar k & phranque

Perl Server Side CGI Scripting Forum

This 95 message thread spans 4 pages: 95 ( [1] 2 3 4 > >     
DB Theory Help for Category Editors Database
Need a little direction please
Nick_W




msg:436655
 12:58 pm on Apr 30, 2003 (gmt 0)

Hi all,

Let's say we have a website like ODP or indeed like WebmasterWorld. Some editors have higer permissions than others right?

Mods have perms in their forum
Admins in their section
BT on everything.
_________________________________________

Here's a simplified DB structure for the site categorie:

categories
id title desc

relationships
catid parent

and let's assume I have a very simple editor tables like:

editors
id status name

perms
editorid catid
______________________________________

So, here's my question (hope your with me so far!):

If I want to make myself have permissions for all categories and there are 100 of them. Do I have to put 100 entries in perms? - Course not, but I don't want to make dozens of calls to the DB just to find out if someone has permissions on a cat.

If you're not quite with me, then neither am I. But weather the above makes sense or not I hope you have an idea of what I'm trying to do.

I'd really appreciate it if someone could give me a shove in the right direction here.

The way I see the above is that I'd have to call up parent of each cat in some kind of recursive loop with many db calls to find out who the 'boss' of a section was (like BT or an Admin here). That seems silly.

Is there a better way, am I over complicating this?

Many thanks!

Nick

 

lorax




msg:436656
 1:10 pm on Apr 30, 2003 (gmt 0)

Nick,
Is it possible to create groups of permissions. Take for example yourself - GOD. GOD gets access to everything so the page code would check for the existence of group permissions first and then look to individually set permissions. Will this work for you?

Nick_W




msg:436657
 1:13 pm on Apr 30, 2003 (gmt 0)

Could do...

In fact I think we discussed it a little while back in a wider context. Can you elaborate any? - did you get what I meant above?

Nick

grahamstewart




msg:436658
 1:15 pm on Apr 30, 2003 (gmt 0)

Don't split relationships out of categories it doesn't gain you anything and DB theory says that your tables should represent real things (ie the nouns).

So categories should look like..

id ¦ title ¦ desc ¦ parent

Now let me think about this permissions thing....

lorax




msg:436659
 1:29 pm on Apr 30, 2003 (gmt 0)

I understand what you're after though I'm not sure I understand what the purpose of the relationships
table is.

What I'm suggesting is that you develop a heirarchy if there is reliable way to group editorial permissions. Take for example WebmasterWorld. We have a Cat called "The Webmaster World". Under that we have the topics of "Perl and PHP CGI Scripting" and "Tracking and Logging" etc...

I could divinde the whole category up into 3 sub-cats "Software", "Hardware", "Theory". This would give me three levels of permissions.

GOD - access to the entire category
2nd Tier - Access to one or more of the sub-cats and
Individual - Access to one or more of the topics.

So I would set a session var when the editor logs in that I would check when they go to their Editor greeting page (or whatever the next page is after they've logged in). Here I'd only present them with what they have access to based on the permissions var. That var would be an array of the permissions they have. If $perm[0] == "GOD" they get everything. If $perm[0] == "software" that's all they get.

Make sense or have I wondered off what you're really after?

grahamstewart




msg:436660
 1:35 pm on Apr 30, 2003 (gmt 0)

Hmm.. can't see a way past recursing up the category heirarchy.

However, as the others pointed out, you could shortcut this by having types of editor, so you only recurse up when you know that the editor is a 'section editor'.

How many categories deep were you imagining the heirarchy would be?
If its hundreds then the approach will make a big difference, but if its only two or three you probably won't notice.

Nick_W




msg:436661
 1:41 pm on Apr 30, 2003 (gmt 0)

>purpose of the relationships

Think I'm just over zealous on the normalization! ;)

>wander off

Nope, think we're on track here lorax!

Though I think I'd still need a table for which cat they had been assigned to right?

So, if I'm mod'ing CSS, somehow the software needs to know that. But, I'd like RC to be my boss and have perms on all his sub-cats, and BT to be his boss and be GOD right?

So if you have:

/my-dog/needs-a/walk/soon

and I'm editing soon, I cant do anything else, unless soon develops a sub cat.

Everyone above me can edit my cat but needs can only edit walk and soon.

I know we're on track but making it really clear may help ;)

Essentially it's a hierachy thing in line with the categories table and the relationships outlined in the parent column.

...phew!

Many thanks guys, this stuff is Invaluable!

Nick

Nick_W




msg:436662
 1:42 pm on Apr 30, 2003 (gmt 0)

>How many categories deep were you imagining the heirarchy would be?

Infinate.
I'm building it for me, but I've spent so much time on it I need it to be seriously scalable and adaptable...

Nick

daisho




msg:436663
 1:44 pm on Apr 30, 2003 (gmt 0)

I look at this a little differently and like the way you were going at it first Nick. I would seperate the permissions from the categories. That gives you the most felxability down the road but in the short term gives you a little more coding. I recenty had to add a security layer to a web based system that was already in place and this is the appoach I took:

tblaccess_right
id varchar(20) primary key
desc varchar(500)

tblgroup
id varchar(20) primary key
desc varchar(500)

tbluser
login varchar(20) primary key
password varchar(20)
[any other fields you want]

tbluser_right
id
login

tblgroup_rights
id # from tblaccess_rights
id # from tblgroup

tbluser_group
user
id

Now this gives you a somewhat generic security model. As user can be part of any group or access right. And groups can contain 1 or many access rights.

With this setup you can create a single SQL statement that returns all the access rights avalible to the user be it from a direct grant or inherited from a group.

Now in most cases I'd simply store all the rights in an array. Before you display a certain button of feature (example in php) do:

if( isset( $_SESSIONS['access_rights']['killthread'] ) ) {
// Show the button
}

As a side note I normaly make my Primary Keys a number. In this case I would make it a string and in the code example above the reasoning becomes clear, it make your code much more readable.

I'm not sure if this is on the track that your looking for it's just a model that I setup in the past and it adds quite a bit of flexability.

daisho

brotherhood of LAN




msg:436664
 1:44 pm on Apr 30, 2003 (gmt 0)

>>>If I want to make myself have permissions for all categories and there are 100 of them. Do I have to put 100 entries in perms?

I suppose you'll have someone logged in when they are attempting to edit, so you have a few variables to play with courtesy of them being there.....

Since they want to edit an article you'll also have the article ID. And since the articles are in a particular category you know the categoryID too, so you'll know how to get the admin by knowing the editorsid,articleid and categoryid.

Since each category has an admin, you should know what admin is in charge of what category.

In the case of BT, or someone who can edit anything, I'd have a special value for them, i.e. NULL. Every other admin/editor has an integer ID though BT would have NULL. So..

>>>If I want to make myself have permissions for all categories and there are 100 of them. Do I have to put 100 entries in perms?

You shouldn't have to. If you were the logged in user and your editorID was NULL, then you could have rights to edit. Anyone with an INT as an ID would have to be checked.

So I'd add an adminID to each category to complete the missing link.

categories
id title desc

relationships
catid parent

Another way of doing this is to add another field in categories, the "parent". When the parent = "NULL", it is the top directory. Saves a few bytes.

I was slow on writing this, offline, hope theres not too much overlap ;)

ShawnR




msg:436665
 1:52 pm on Apr 30, 2003 (gmt 0)

I agree with "..Don't split relationships out of categories..". If the relationship is 1 to many (i.e any category can only have one parent), than just have the foreign key (which in this case is the local key as well) in the category table. If the relationship is m to n, you need a separate relationship table.

Is it possible for an editor to have 2 roles? e.g. mod for "css" and admin for "Search engine world" at the same time? If not, you could remove the 'perms' table, and put catid in editors table.

You could do the hierarchy in the categories table, or if you know the hierarchy will only ever be 3 deep, you could do it as lorax suggests; amounts to the same thing.

So I'd suggest pretty much what you had. i.e:


categories
catID title desc parent_catID


editors
edID status name catID

It is not really that many calls. Given a particular editor and a particular category, there is one call to find out which category the ed has permissions on, and a max of 2 calls up the categories hierarchy if the hierarchy is 3 high as in mods->admins->BT

Shawn

<added... Seems a few posts hae snuck in since the last I saw before posting...
"...I think I'd still need a table for which cat they had been assigned to right? ..."
No, I think if the relationship is an editor only looks after one category, no matter how high in the hierarchy that category is, you don't need the relationships table. Only if the relationship is m-n.

"...How many categories deep? Infinate. ..."
Well that makes it more interesting...in which case the above might not be the best way to go, and the fastest code is unlikely to be normalised code.
/added>

[edited by: ShawnR at 2:29 pm (utc) on April 30, 2003]

Nick_W




msg:436666
 1:58 pm on Apr 30, 2003 (gmt 0)

>>Is it possible for an editor to have 2 roles? e.g. mod for "css" and admin for "Search engine world"

No, strict hierachy. The theory is simple, cats above you can edit yours, cats below you can be edited by you.

Problem is (;) and there always is one) that it needs to be really scalable. I'll probably not use it for more than 3 deep but I'd like to never have to upgrade the DB, maybe the code but I'd like to be able to use the same structure for a 10 categories deep structure as 3.

With me? ;)

BOL and Daisho - WOW - Still trying to get my brain round your posts! ;-) - thanks!

Nick

lorax




msg:436667
 1:59 pm on Apr 30, 2003 (gmt 0)

>> So, if I'm mod'ing CSS, somehow the software needs to know that.

Correct. When you login the app will query the db for your permissions. These are stuffed into the array. The possible permissions are GOD, sub-cat(s), and topic(s). So if the app finds GOD permissions - it stops searching and sets the permission var. If not - checks for sub-cats and so on.

As daisho noted, this structure is NOT scalable as it assumes that everything stops at a certain number of levels.

daisho I'm not quite clear on how you're using the different tables. An example?

/my-dog/needs-a/walk/soon
and I'm editing soon, I cant do anything else, unless soon develops a sub cat.
Everyone above me can edit my cat but needs can only edit walk and soon.

You could do it this way. Your permissions would then need to include the ability to add sub-cats and assign permissions to those below you in the pyramid as well as provide those above you the ability to edit/oversee everthing everyone else is creating. Depending upon how much activity there is - this could get messy very quickly.

lorax




msg:436668
 2:10 pm on Apr 30, 2003 (gmt 0)

Problem is (and there always is one) that it needs to be really scalable. I'll probably not use it for more than 3 deep but I'd like to never have to upgrade the DB, maybe the code but I'd like to be able to use the same structure for a 10 categories deep structure as 3.

Ok. One method to make the structure of my suggestion scalable is to add an additional table that defines the categorical relationships.

major_cat ¦ cats

Major Cats could be given any name (Bob if you'd like) and it could contain any number of categories. So if 'soon' suddenly decides to add a category, a major cat called 'soon' would appear and any categories that he added would show up in the cats field.

This would allow for growth but again, the level of complexity could grow quickly.

ShawnR




msg:436669
 2:15 pm on Apr 30, 2003 (gmt 0)

Well, firstly, a big congratulations for post which is generating so much enthusiasm... seems everyone is getting caught by submitting a post only to find they are 3 or 4 posts behind...

On the one hand "...How many categories deep? - Infinate...", and on the other hand "...I'd like to be able to use the same structure for a 10 categories deep ..." I understand that from a purist perspective you'd like it to be 'infinitely' scalable, but it appears we are not really talking about 100's or 1000's of levels. Perhaps 100's or 1000's of categories, but not levels. In which case I'd suggest just go with

categories
catID title desc parent_catID

editors
edID status name catID

No fudging, normalised, simple. Maximum lookups = number of levels inhierarchy.

daisho




msg:436670
 2:20 pm on Apr 30, 2003 (gmt 0)

Lorax,

The master table is really tblaccess_right that is the table that contains all the actions or permissions that you want to test for.

You ofcourse have users in tbluser. A user can be part of a group or be granted a specific access right.

Since these are many-to-many the other tables are used to link users to rights, users to groups, groups to rights.

Then 1 SQL can return all rights for a user. Put that in an array and do your tests.

I do like the idea of your "GOD" permission it's somthing that I've been meaning to add to my model since we sometimes forget to add the admins to a group when we add a new feature.

Though froms Nick's description this model will need to change a little bit since it doesn't really support infinate levels without infinate permissions.

daisho

Nick_W




msg:436671
 2:25 pm on Apr 30, 2003 (gmt 0)

What ShawnR says seems very reasonable. Though I do really like the permissions thing too (god, admin, cat-mod).

Okay, so it's not 1000's maybe 3-4 max average.

Daisho, you're just way beyond me fella!

What's the consensus then guys, how do I build it?

;);) ---thanks!

Nick

lorax




msg:436672
 2:37 pm on Apr 30, 2003 (gmt 0)

The only problem I see with ShawnR's suggestion of

categories
catID title desc parent_catID

editors
edID status name catID

is what happens when you add a category. You will physically need to edit each user that you want to give permissions to the new category.

The heirarchy model I'm suggesting is based off of Novel's NDSAdmin structure. If I 'trust' (give permissions) to someone below my level then I'll always have permission to anything below me. And I can't see anything above me or on the same level as me unless you give me permissions.

If you realistically are only going to go to maybe 10 levels then this should work fine.

Daisho's model seems intriguing but it does require a bit more up front work. The flexibility is nice and necessary if you think you might want to step outside of the heirarchy someday. The base concept of building for scalability may be prudent here. But that's your decision mate! ;)

daisho




msg:436673
 2:44 pm on Apr 30, 2003 (gmt 0)

Lorax I really like your model but then again I'm a long time fan of NDS. It looks like for this situation your solutions may be the best if it will always be a hiearchy model with higher levels always having access to lower.

I guess we shouldn't start talking about Inherited Rights Filters though eh?

daisho.

ShawnR




msg:436674
 2:47 pm on Apr 30, 2003 (gmt 0)

If you want it to be "future proof", rather than "inifintely scalable", I'd go for the security layer concept. i.e. You may want to allow m-n relationship between editors and categories. e.g. Tedster is on holiday, so Nick, can you mod for both CSS & HTML for a few weeks.

categories
catID title desc parent_catID

editors
edID status name Security_profileID

security_profile
unused_pkID ¦ Security_profileID ¦ catID ¦ perm

where perm = type of permission (read, write, delete, etc)

This is a slightly simplified version of what daisho suggested.

Shawn

ShawnR




msg:436675
 2:50 pm on Apr 30, 2003 (gmt 0)

The only problem I see with ShawnR's suggestion of
categories
catID title desc parent_catID

editors
edID status name catID

is what happens when you add a category. You will physically need to edit each user

Noooo! Its completely hierarchical. As I indicated in previous posts, the only caveat is it assume a 1-n relationship between editors and cats

Shawn

lorax




msg:436676
 3:07 pm on Apr 30, 2003 (gmt 0)

>> As I indicated in previous posts, the only caveat is it assume a 1-n relationship between editors and cats

I see that. But the table editors uses

edID status name catID

so the Cats that an editor may edit are all identified in catID right? Like so "ID1,ID3,ID7". A simple query pulls the IDs from the db and seperates them into an array which you'd then use for checking permission. If so, and I add a future category, then I must go back to each record and add another catID to allow that editor access to that new category. If not, please correct me. :)

ShawnR




msg:436677
 3:19 pm on Apr 30, 2003 (gmt 0)

"...it assume a 1-n relationship between editors and cats
I see that..."

No, you don't. If you thought I meant stringing together a list of cats then that is effectively a n-m relationship.

"...If not, please correct me..."

Will do. I hope I haven't missed your point...

No, I'm not suggesting stringing together a list of cats in a string... yuk, that would make it not normalised, apart from anything else.

What I am suggesting is theoretically exactly the same as the hierarchical model lorax suggested, with only one difference: It can scale in the number of levels in the hierarchy.

e.g.


categories
catID title desc parent_catID
01 GOD null
02 section1 01
03 section2 01
04 forum11 02
05 forum12 02
06 forum21 03
07 forum22 03


editors
edID status name catID
01 BT 01
02 admin1 02
03 mod1 04

So take as an example BT:
CatID = 01, so he is in the GOD category
section1 has parent_catID =01, so BT can edit that
section2 has parent_catID =01, so BT can edit that
forum11 has parent_catID=02, which is section1 so BT can edit that
forum12 has parent_catID=02, which is section1 so BT can edit that
forum21 has parent_catID=03, which is section2 so BT can edit that
forum22 has parent_catID=03, which is section2 so BT can edit that

On the otehr hand, take mod1. CatID =04, so all he gets to edit is forum22, because forum22 doesn't have any children

With me? or have I missed the point?

Shawn

lorax




msg:436678
 3:42 pm on Apr 30, 2003 (gmt 0)

Ok, I think I'm with you now. So you're concept takes the heirarchy concept and provides another option for a scalable structure.

>> yuk, that would make it not normalised, apart from anything else.

Well how far to go with normalization depends upon the number of levels required. It's a judgement call. If we want true scalability then you're absolutely right. I tend to err on the side of practicality in these cases. If I know it's not going to go deeper than 10 levels than I might (operative word) just use the multiple IDs in a single field. But there is definately something to be said re: developing the concept to the point you put forth as it won't become cumbersome as the complexity grows.

Nick_W




msg:436679
 4:04 pm on Apr 30, 2003 (gmt 0)

I get ShawnR's idea, it's pretty much what I'd envisioned but it leaves us with a problem if we want to have perms on adjacent cats.

I think the way it works here at WebmasterWorld is great, I'd just like to expand upon it to make it hierachical also.

So, is there a way to use Shawns approach AND allow a editor to have 2 or more cats? - Like Mivox does FOO and Graphics?

Awsome thread here guys, this one is getting bookmarked for further reading regardless of how it gets set up eventually. I reckon we got a cool DB crowd on this (with 1 obvious exception ;))

Nick

aspdaddy




msg:436680
 4:39 pm on Apr 30, 2003 (gmt 0)

Whenever you have a model working, but the problem is you need more records per entity, you can add a link group - to break the many-to-many relationship.

ShawnR said:
Categories
Editors

But - categories-editors is a many-to-many relation because:

1 category can have many editors
1 editor can have many categories

So, the link group :

isAnEditorOf (#catid, #editorid),
with a composite key would solve it.

Make sense?

lorax




msg:436681
 4:42 pm on Apr 30, 2003 (gmt 0)

The structure as ShawnR and I discussed is based on a pyramid. So if you're at the top of your pyramid you can only see what's beneath you. I.E. if I'm GOD then I see everything in the db/site. If I'm 'soon' then I only see what is beneath me and not in those categories next to or above me.

So in order to accomplish what you're after, I think you'll need to give daisho's idea a try or use the concept I proposed of fixed categories.

Other options folks?

Nick_W




msg:436682
 4:43 pm on Apr 30, 2003 (gmt 0)

Makes sense to me, isn't that what I posted in msg1?

Nick

daisho




msg:436683
 4:51 pm on Apr 30, 2003 (gmt 0)

I've been thinking some on this and I think you can add the inheritance that lorax talks about without to much difficulty to my scheme.

If you are interested I have made an administration module and a base security class for my model. They are written in PHP but the backend used OCI8 (Oracle). Though from that base it would not be to hard to convert to mySQL or a generic PEAR implementation.

I'd be happy to pull out my module and provide it to anyone interested. With a bit of work we would incorporate lorax's top down idea to save even more in administration.

daisho.

lorax




msg:436684
 4:51 pm on Apr 30, 2003 (gmt 0)

>> isAnEditorOf (#catid, #editorid), with a composite key would solve it.

I'm not sure what you mean by a link group. I like the concept, I'm just not sure how you mean to execute it - isAnEditorOf a new table?

This 95 message thread spans 4 pages: 95 ( [1] 2 3 4 > >
Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved