Tree storage with companies / supervisors / workers

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
deadoralive
Forum Commoner
Posts: 28
Joined: Tue Nov 06, 2007 1:24 pm

Tree storage with companies / supervisors / workers

Post 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
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post 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.
deadoralive
Forum Commoner
Posts: 28
Joined: Tue Nov 06, 2007 1:24 pm

Post 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
willearp
Forum Newbie
Posts: 3
Joined: Wed Nov 07, 2007 3:48 am

Tree storage with companies / supervisors / workers

Post 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
deadoralive
Forum Commoner
Posts: 28
Joined: Tue Nov 06, 2007 1:24 pm

Post 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!
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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.
deadoralive
Forum Commoner
Posts: 28
Joined: Tue Nov 06, 2007 1:24 pm

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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.
deadoralive
Forum Commoner
Posts: 28
Joined: Tue Nov 06, 2007 1:24 pm

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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.
deadoralive
Forum Commoner
Posts: 28
Joined: Tue Nov 06, 2007 1:24 pm

Post 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!
Post Reply