Page 1 of 1

3D Database Model: Implementation?

Posted: Thu May 29, 2008 11:42 am
by Verminox
I require something of a 3-Dimensional database model (as opposted to the conventional 2D 'tables') and I don't know how to implement it.

I have a 'documents' table containing a whole list of documents and a 'users' table containing registered users. Now I want to map this onto a 'permissions' model that will give me the permissions a user has for a particular document (eg. can_edit,can_delete,etc).

My first attempt at an implementation was something like this:

Code: Select all

TABLE: permissions
id | document_id | user_id | can_edit | can_delete
So when a user tries to perform an action, I can get the permissions using:

Code: Select all

SELECT `can_edit` FROM `permissions` WHERE `document_id` = 10 AND `user_id` = 2
However, I realised that there is a problem. If I have 'm' number of users and 'n' number of documents, and I create a new document, I will make to insert 'm' number of rows, each mapping to a user and the new document. It gets even worse if I create a new user, I will have to insert 'n' numbers of rows for permissions of this user to each document. Even I have few users (m < 20), I can have a whole load of documents (n>10000).... this means 10000 insert queries each time I create a new document :|

Even I use 'user groups' instead of individual users to match permissions, the same issue occurs when I add a new user group.

So I need some kind of 3D model for permissions. Documents on the X axis, Users on the Y axis, and Permissions on the Z axis. So each time I create a new user, it just fills up all of it's releated X and Z co-ordinates with default values specified in the model.

Any suggestions?

Re: 3D Database Model: Implementation?

Posted: Thu May 29, 2008 1:04 pm
by dml
Can you not just represent it sparsely, where the absence of a row in the table means the absence of the permission?

Code: Select all

 
TABLE: permissions
id | document_id | user_id | action: enum('edit', 'delete')
 
SELECT count(*) FROM `permissions` WHERE `document_id` = 10 AND `user_id` = 2 && action='edit'
 
 

Re: 3D Database Model: Implementation?

Posted: Thu May 29, 2008 1:41 pm
by califdon
That's more or less what I was going to suggest. Assign default permissions, based on which is more common, granted or not-granted, then only store the exceptions from default. When testing, if there is no matching record, it is the default.

If your usage patterns fit a group structure, I would recommend that.

I think I have read about 3-D databases, but I don't know of any implementations, and I'm not sure they would be any more efficient than the straightforward relational model you alluded to. Fundamentally, every time you add a new user, you're going to have to make as many decisions as there are documents, one way or another, unless you can use groups.

Another approach that could be considered, depending on the distribution of the permissions required, might be just to link each user to all the documents for which that user has write permissions, through an intermediate many-to-many table. As an example, I have a very small prototype amateur poetry site (http://poatree.org -- please excuse the terrible pun) where everybody can read the poems, but only the author can edit his or her own poems. That's easy, because it's always clear who has write permission. Is it possible there's some criteria like that, that you could apply in your case?

Re: 3D Database Model: Implementation?

Posted: Thu May 29, 2008 11:15 pm
by Verminox
Thanks for the replies dml and califdon. I'm thinking of implementing a hybrid of default-when-unset and inherit-from-parent, because my document structure is hiearchical (pages can have subpages).

For example, there could be a document /Computers/Programming/PHP/Syntax. If there is no permission set for the current user and Syntax, it will check its parent (the PHP Page). If not, it will check the Programming page, etc. This will go on until it finds a permission set. If even the top level page has no corresponding permission set, then defaults are used. This makes it easier because each time a new document is created all the admin has to choose for permissions is 'Inherit from parent' or 'Set manually'. Changing from manual to inherit deletes all associated rows automatically.

I was also thinking of maybe giving documents statuses, such as 'Locked','Open','Draft',etc. so instead of mapping users to documents, I could map User Groups to Status (Editors can edit drafts, Admins can see Locked, Users only open)... but this would severely limit the customizability of the application (eg. I won't be able to restrict Editors from editing drafts in /Company).

Re: 3D Database Model: Implementation?

Posted: Thu May 29, 2008 11:19 pm
by califdon
That sounds like it should work. The devil is in the details, of course.