3D Database Model: Implementation?
Posted: Thu May 29, 2008 11:42 am
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:
So when a user tries to perform an action, I can get the permissions using:
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?
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_deleteCode: Select all
SELECT `can_edit` FROM `permissions` WHERE `document_id` = 10 AND `user_id` = 2Even 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?