user(group) - item(folder), multi-root database with tree structure

¡Ay, caramba! -

I potentially have two DB tables- The first one a user(folder) table which is a tree structure(parent child)(usersorganised into clients开发者_如何学Python or deptartments) Its fully editable so new structure(parentid left right etc) needs to be re-written pretty often

I then have an item(folder) table tree structure - Same thing - editable so add, delete, copy, paste, move around... as in http://www.jstree.com/demo - (the PHP MYSQL editable functionality)

NB I expect the depth of the tree to rarely be more than 5 so depth first is not really necessary...although I am considering it (safer) - there are however going to be one hell of a lot of items and potentially users.

Just looking for a bit of direction after googleing for ages... Problem 1 - One table or two? - One table would be lovely ...only the entire DBtable would need to be re-written on a save if Im moving folders around. Considering that this structure needs to monitor all users and item...its a big SQL UPDATE

Problem 2 - Multiple root tree structures... If i just wanted to access the structure at say...user 5(root) play about and save a portion of the tree I'm opening myself up to left/right / id's being corrupt.

Basically the requirements are to have users login - They can then manage their user structure which can be organised into folders(clients). Then allow those logged in users to add item folders(organizing items) to other users and have the functionality to be able to copy(add,rename,move,delete) item folders across users...so that those users (when logged in) can only view those items.

pheew. hope i've explained this ok

I spoke to one of our top DBAs for some assistance on the matter.

The principle problem with these types of design is not the number of tables but the type of use.

If it is a single user system the use of one table is not a problem, it is a little messy to manage, but the merge command can help here.

If the data is to be accessed simultaneously by a number of users this kind of structure can cause locking problems.

"I would go for two tables with the first holding the data and second holding just the relationship information parent to child. If you have multiple relationships between items I.e a parent having many children it would have multiple rows in the relationship table. (Oracle have adopted this approach for their foreign key relationships (Parent- Child))"

Hope this helps