Tree storage with companies / supervisors / workers
Moderator: General Moderators
-
deadoralive
- Forum Commoner
- Posts: 28
- Joined: Tue Nov 06, 2007 1:24 pm
Tree storage with companies / supervisors / workers
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
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
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
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.
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
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.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.
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
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
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
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!
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!
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
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 wrote:...this occurs so infrequently...
-
deadoralive
- Forum Commoner
- Posts: 28
- Joined: Tue Nov 06, 2007 1:24 pm
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.CoderGoblin wrote: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 wrote:...this occurs so infrequently...
Thanks for all the advice, you've all been very helpful
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.
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
Hi and thanks for the welcome!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.
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 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
-
deadoralive
- Forum Commoner
- Posts: 28
- Joined: Tue Nov 06, 2007 1:24 pm
Hi,califdon wrote: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 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 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!