Page 1 of 1

Tree storage with companies / supervisors / workers

Posted: Tue Nov 06, 2007 1:42 pm
by deadoralive
Hi all,

I'm a long time reader of this forum and have read a lot just looking around the place. However thought it was about time I got involved, and I also have a question to ask about a database design i'm creating. Please bear with me though as this may end up quite lengthy.

Basically I have 3 different user types in a system I'm creating, these are Companies, Supervisors, and Workers. Each user will need to log in and will have varying access to the system depending on their type. The permissions side of things is out of the scope of this thread however thought i'd mention it as i will need to expand on my design to include this.

The complication with this is storing the tree like relationship between these users. for example

Company A
- Supervisor A
- Worker A
- Worker B
- Supervisor B
- Worker C
- Worker D
Company B
- Supervisor C
- Worker E

A little bit of explanation. Basically a company may have any number of supervisors, and a supervisor may have any number of workers. Sounds like basic many to many relationships right, seperate tables seem to be a good way to go...

However Companies / Supervisors / Workers share a great deal of common functionality. For example a company may want to send a message to another company / supervisor / worker. A worker may want to send a message to his superior, or his or another company. This would be simplified if all users were stored in a single table, with a user type field.

Basically i'm torn between two database designs

Design A

- 3 seperate tables for each

company

company_id
company_name
username
password

supervisor

supervisor_id
supervisor_name
username
password
company_id

worker

worker_id
worker_name
username
password
supervisor_id

This design would make storing the user details easy, but would triple the complexity of the login script, and probably the rest of the functionality as well.

Design b

One single table for users

users

user_id
name
username
password
user_type_id
parent_company_id
parent_supervisor_id

user_type
user_type_id
user_type_title

Anyway I realise this is a very long post for my first time on the forum, so i'll shut up now. Just wanted to get some opinions on best practice / experience etc.

Thanks for reading

Posted: Tue Nov 06, 2007 2:28 pm
by Kieran Huggins
Hi DOA, Welcome to the non-lurking side!

Take a look at Modified Preorder Tree Traversal - it's the technique Rails uses in "acts_as_tree".

On the plus side, it adds a flexible hierarchy to your existing table (one table, no need to add more). It can also pull the entire tree out of SQL in just one query.

Unfortunately, modifying any node in the tree updates every tree node, which could be expensive.

You could also use a separate "relationship" table where you join one record to one or many others with a relationship type:

relationships

user_id
foreign_user_id
rel_type

That would of course mean many queries to build a tree, but it would be less expensive to update.

Posted: Tue Nov 06, 2007 3:05 pm
by deadoralive
Kieran Huggins wrote:Hi DOA, Welcome to the non-lurking side!

Take a look at Modified Preorder Tree Traversal - it's the technique Rails uses in "acts_as_tree".

On the plus side, it adds a flexible hierarchy to your existing table (one table, no need to add more). It can also pull the entire tree out of SQL in just one query.

Unfortunately, modifying any node in the tree updates every tree node, which could be expensive.

You could also use a separate "relationship" table where you join one record to one or many others with a relationship type:

relationships

user_id
foreign_user_id
rel_type

That would of course mean many queries to build a tree, but it would be less expensive to update.
I Keiran thanks for the welcome, and thanks for the link you've posted. I've seen that before for navigation menus and found it to be very helpful. I'm not really concerned about having to pull the entire tree out in one query, as more than likely i wont have to pull the entire tree out on one page.

However your second suggestion about the database design may be something to look at, i'll have to think about that. After re-reading my post again i will more than likely use the single table design, will just simplify so may different areas of the site.

Any other suggestions are more than welcome :-)

Thanks again

Tree storage with companies / supervisors / workers

Posted: Wed Nov 07, 2007 4:17 am
by willearp
Hi deadoralive,

I am a little confused on the logical separation of your entities. you have companies as the root nodes of your hierarchy, but these companies are actually managers? If this is the case then it will not change how you want to stucture it, and just makes it easier to think about.

So as long as each user only has one manager / supervisor / person above them, then I think you should have everyone in one table and have a parent field to indicate who is under who in you tree structure, the root managers will therefor have no parent, or 0, and anyone below them will store their parents' userID as their parentID.

This structure is easy to update, and fairly easy to use. Actually drawing the whole tree is a little more complex, but for login purposes, I don't think your program needs to know about these relationships

Good Luck!

Will Earp

Posted: Wed Nov 07, 2007 4:54 am
by deadoralive
Hi willearp,

Everything you've said is correct. The Company is actually the top level user for a group of supervisors / workers, just his / her details will refer to the company and not a specific person within the organisation.

The more i think about it the more it makes sence to use the single table. As you said their is a little more effort to display the company / supervisor / worker tree, however every other element of the system will be mch simpler to deal with. I'm 99% sure there will always only be one parent user, i'll have to clear this up before i progress any futher. Occasionally there is job sharing to worry about between supervisors but this occurs so infrequently that it may be worth just having a single account for the two people job sharing, and not worrying about it any further.

Thanks for all the help!

Posted: Wed Nov 07, 2007 7:37 am
by CoderGoblin
deadoralive wrote:...this occurs so infrequently...
Be aware lots of projects I have known have taken assumptions like this and found out later that they are far more common than the developers were led to believe. Design the system to be as robust and as flexible as possible without 'hacks' for what you are told are infrequent problems.

Posted: Thu Nov 08, 2007 6:14 am
by deadoralive
CoderGoblin wrote:
deadoralive wrote:...this occurs so infrequently...
Be aware lots of projects I have known have taken assumptions like this and found out later that they are far more common than the developers were led to believe. Design the system to be as robust and as flexible as possible without 'hacks' for what you are told are infrequent problems.
Valid point. However i'm not sure if using the same account for job sharing people would be considered a 'hack' really. I'll have to find out if this is really an issue before proceeding.

Thanks for all the advice, you've all been very helpful

Posted: Thu Nov 08, 2007 9:05 am
by califdon
What you are discussing is establishing a relational database schema. While there are surely alternative approaches, there is usually a single best design, based on entity analysis. I don't see any tree structure involved in the situation you described. Maybe that's because I'm experienced with relational databases, which don't map very well to hierarchical data. You know, to a man who only has a hammer, everything looks like a nail!

Entity analysis is the approach that is recommended for every relational database design. A relational database is a model of some segment of the real world. Begin by establishing what entities you will represent in the database, then what attributes they possess. You did begin that way, with entities Company, Supervisor and Worker. In my opinion, that answers the question; three tables. Could you combine them into a single table? Well, probably yes, but that would be saying that they are all the same entity, which I doubt is true. Perhaps Supervisors and Workers could be considered the same entity, with an attribute (field) that indicates whether or not they are a supervisor, but it's a stretch to say that a Company is the same entity as a human being. Why is this important? Because even if right now you don't have any fields that would only pertain to one or the other, the real fact is that companies and people do have different attributes, and some day you (or your successor) may want to add attributes that apply to only one entity or the other. Databases should be designed to be extendable, not just made to work for one special case. It rarely requires any more work to design something correctly to begin with, than to slap something together, especially if you have to start all over again next year because your initial design was wrong, even if it worked.

Glad to have you become active in the forum.

Posted: Thu Nov 08, 2007 5:37 pm
by deadoralive
califdon wrote:What you are discussing is establishing a relational database schema. While there are surely alternative approaches, there is usually a single best design, based on entity analysis. I don't see any tree structure involved in the situation you described. Maybe that's because I'm experienced with relational databases, which don't map very well to hierarchical data. You know, to a man who only has a hammer, everything looks like a nail!

Entity analysis is the approach that is recommended for every relational database design. A relational database is a model of some segment of the real world. Begin by establishing what entities you will represent in the database, then what attributes they possess. You did begin that way, with entities Company, Supervisor and Worker. In my opinion, that answers the question; three tables. Could you combine them into a single table? Well, probably yes, but that would be saying that they are all the same entity, which I doubt is true. Perhaps Supervisors and Workers could be considered the same entity, with an attribute (field) that indicates whether or not they are a supervisor, but it's a stretch to say that a Company is the same entity as a human being. Why is this important? Because even if right now you don't have any fields that would only pertain to one or the other, the real fact is that companies and people do have different attributes, and some day you (or your successor) may want to add attributes that apply to only one entity or the other. Databases should be designed to be extendable, not just made to work for one special case. It rarely requires any more work to design something correctly to begin with, than to slap something together, especially if you have to start all over again next year because your initial design was wrong, even if it worked.

Glad to have you become active in the forum.
Hi and thanks for the welcome!

While I respect and appreciate your willingness to help, I have to disagree with some of your comments. All three entities (Company / Supervisor / Workers) are all users within the system with varying levels of access. Each entity type will require a username / password combination, and will share a large amount of common values. As stated previously perhaps using "Company" as the name of the top level entity is a bit misleading, a better term would be manager.

I need to store the relationship between Manager / Supervisor / Worker as obviously a supervisor needs to know who his / her staff are (etc etc). However as mentioned I also need to provide other functionality in other tables that should link to a user (such as private messaging), whether that user is a Manager / Supervisor or a Worker. Having their details spread over 3 tables would make this much more complex. Perhaps I have over complicated the explanation in my initial post, if this is the case I apologise.

Having said all that however the reason I opened this thread was to get opinions on my design, so thank you for your input. I'm going to re-read your comments and make sure I am correct in my assumptions.

Thanks again

Posted: Sat Nov 10, 2007 5:08 pm
by califdon
deadoralive wrote: Hi and thanks for the welcome!

While I respect and appreciate your willingness to help, I have to disagree with some of your comments. All three entities (Company / Supervisor / Workers) are all users within the system with varying levels of access. Each entity type will require a username / password combination, and will share a large amount of common values. As stated previously perhaps using "Company" as the name of the top level entity is a bit misleading, a better term would be manager.

I need to store the relationship between Manager / Supervisor / Worker as obviously a supervisor needs to know who his / her staff are (etc etc). However as mentioned I also need to provide other functionality in other tables that should link to a user (such as private messaging), whether that user is a Manager / Supervisor or a Worker. Having their details spread over 3 tables would make this much more complex. Perhaps I have over complicated the explanation in my initial post, if this is the case I apologise.

Having said all that however the reason I opened this thread was to get opinions on my design, so thank you for your input. I'm going to re-read your comments and make sure I am correct in my assumptions.

Thanks again
I may have leapt to a conclusion when I read "Company". I assumed that it refers to an actual company. Perhaps you could explain what these entities really represent, then. My basic point, however, is that it all depends on defining the entities. As a teacher and a practitioner, I have found literally hundreds of examples of people trying to design relational table schemas and in virtually every case, their difficulties are the result of "fuzzy" concepts of what entities they need to represent in their database. So my recommendation remains the same: take the time to explicitly define each entity that you need to represent. Avoid thinking in terms of "I need to do such-and-such" because that leapfrogs the step of defining the entities. My advice comes from a lot of years of experience with databases and with people designing databases.

Posted: Mon Nov 12, 2007 2:42 pm
by deadoralive
califdon wrote:
deadoralive wrote: Hi and thanks for the welcome!

While I respect and appreciate your willingness to help, I have to disagree with some of your comments. All three entities (Company / Supervisor / Workers) are all users within the system with varying levels of access. Each entity type will require a username / password combination, and will share a large amount of common values. As stated previously perhaps using "Company" as the name of the top level entity is a bit misleading, a better term would be manager.

I need to store the relationship between Manager / Supervisor / Worker as obviously a supervisor needs to know who his / her staff are (etc etc). However as mentioned I also need to provide other functionality in other tables that should link to a user (such as private messaging), whether that user is a Manager / Supervisor or a Worker. Having their details spread over 3 tables would make this much more complex. Perhaps I have over complicated the explanation in my initial post, if this is the case I apologise.

Having said all that however the reason I opened this thread was to get opinions on my design, so thank you for your input. I'm going to re-read your comments and make sure I am correct in my assumptions.

Thanks again
I may have leapt to a conclusion when I read "Company". I assumed that it refers to an actual company. Perhaps you could explain what these entities really represent, then. My basic point, however, is that it all depends on defining the entities. As a teacher and a practitioner, I have found literally hundreds of examples of people trying to design relational table schemas and in virtually every case, their difficulties are the result of "fuzzy" concepts of what entities they need to represent in their database. So my recommendation remains the same: take the time to explicitly define each entity that you need to represent. Avoid thinking in terms of "I need to do such-and-such" because that leapfrogs the step of defining the entities. My advice comes from a lot of years of experience with databases and with people designing databases.
Hi,

I think i've confused matters as at the start of this thread I was thinking of the company as a company, rather than as an actual user (which is what it is!). To clear things up the company is simply the top level user whose details happen to be that of the company (Company name, address etc). This user will have the highest level of access (aside from admin of course).

I think my main entities in this system are the users with three differing levels (Worker / Supervisor / Company). However my initial confusion came from that I also have to store the relationships between these user levels ( Worker needs to know his supervisor etc ). I'm still not entirely set on the best way to do this because as already mentioned there may be job sharing between supervisors.

Anyway thanks for all the advice, it has been very helpful. Any more insights are always welcome!