Topic: Linking 3 Tables

[TapeGun007]

I just need someone to steer me in the right direction.  I've used minimal SQL to link a couple of tables together, but linking three is beyond my little bit of SQL knowledge.

Here are the tables:

Table: Users
User_ID
First_Name

Table: Department
Department_ID
User_ID
Department_Name

Table: Sub_Department
Sub_Department_ID
Sub_Department_Name

Let's pretend that in the Users Table the ID is 1, and the First_Name is Bob.

Bob is a part of the Music Department, and the Food department in the Department Table.

Under the Music Department, Bob is a part of Sub Departments like Musicians, Team Leader, Choir, and Special Group.

A sub department under the Food Department, would be like Cooking, and Dishes.

I'll be programming this in PHP, and I just want the results from the database to spit out something like this in format:

Bob
  Music
    Musician
    Team Leader
    Choir
    Special Group
  Food
    Cooking
    Dishes

Any help is greatly appreciated!

[TapeGun007]

Forgot to mention, it's mySQL Version: 5.0.45, and I use phpAdmin.

I'm used to using Access, not mySQL and phpAdmin, so I'm a bit new to this as well.

[SchweppesAle]

Well, if it was me I'd create two new columns within the Users table; name them "Department_ID" and "Sub_Department_ID" respectively.  Assuming every user has both a Department and Sub-department(or that those are the only ones that matter in this query), I'd then run something like this.

Code: [Select]

$query = "SELECT Users.First_Name
                , Department.Department_Name
                , Sub_Department.Sub_Department_Name
             FROM Users
                INNER JOIN Department ON
                    (Users.Department_ID = Department.Department_ID)
                INNER JOIN Sub_Department ON
                    (Users.Sub_Department_ID = Sub_Department.Sub_Department_ID)";


[TapeGun007]

No, some users will not belong to any department at all.  Some departments will not have sub-departments either.

Does this change anything in your example to me?

[SchweppesAle]

No, some users will not belong to any department at all.  Some departments will not have sub-departments either.

Does this change anything in your example to me?

you'll need to modify it just a bit then -> http://help.w3schools.com/sql/sql_join_left.asp

[Psycho]

Well, if it was me I'd create two new columns within the Users table; name them "Department_ID" and "Sub_Department_ID" respectively.  Assuming every user has both a Department and Sub-department(or that those are the only ones that matter in this query), I'd then run something like this.

In the original description TapeGun007 declared that the user would be associated with mutiple departments and multiple sub-departments. Trying to jam the department associations into the user table would be a very poor method. The only change *I* would make is to have separate tables for the Department and Sub-department names. Then in the current tables you only need the user ID and the department/sub-department ID to make the association. But, let's go with what you have:

One thing that does not make sense is that there is no association between the sub-departments and either the department or the user. Isn't there a Department_ID field in the Sub_Department table to identify which sub-departmtents belong to which department? Also, should the sub-departments be associated with the users or is it assumed that a user is associated with all the sub-departments of any departments they are associated with?

[SchweppesAle]

Well, if it was me I'd create two new columns within the Users table; name them "Department_ID" and "Sub_Department_ID" respectively.  Assuming every user has both a Department and Sub-department(or that those are the only ones that matter in this query), I'd then run something like this.

In the original description TapeGun007 declared that the user would be associated with mutiple departments and multiple sub-departments. Trying to jam the department associations into the user table would be a very poor method. The only change *I* would make is to have separate tables for the Department and Sub-department names. Then in the current tables you only need the user ID and the department/sub-department ID to make the association. But, let's go with what you have:

One thing that does not make sense is that there is no association between the sub-departments and either the department or the user. Isn't there a Department_ID field in the Sub_Department table to identify which sub-departmtents belong to which department? Also, should the sub-departments be associated with the users or is it assumed that a user is associated with all the sub-departments of any departments they are associated with?

Minus the removal of Department.User_ID, how is that different from what I recommended? 

secondly:

No, some users will not belong to any department at all.  Some departments will not have sub-departments either.

Does this change anything in your example to me?

Code: [Select]

$query = "SELECT Users.First_Name
                , Department.Department_Name
                , Sub_Department.Sub_Department_Name
             FROM Users
                INNER JOIN Department ON
                    (Users.Department_ID = Department.Department_ID)
                INNER JOIN Sub_Department ON
                    (Users.Sub_Department_ID = Sub_Department.Sub_Department_ID)";

becomes

Code: [Select]

$query = "SELECT Users.First_Name
                , Department.Department_Name
                , Sub_Department.Sub_Department_Name
             FROM Users
                LEFT JOIN Department ON
                    (Users.Department_ID = Department.Department_ID)
                LEFT JOIN Sub_Department ON
                    (Users.Sub_Department_ID = Sub_Department.Sub_Department_ID)
                             GROUP BY Users.User_ID";


[Psycho]

Minus the removal of Department.User_ID, how is that different from what I recommended? 

No, it is completely different. You suggested adding two fields to the user table for department and sub-department. However, as per the requirements there is a many-to-one relatinship between users and departments/sub-departments. So, you need a secondary table to associate the users with 0 to many departments/sub-departments. That is database design 101, read up on database normalization. The suggestion I made was totally different. The current structure has users and departments tables (where the users are associated with departments via a foreign key in the departments table. However, this is not normalized as the department names are duplicitive data. The Departments should be unique records and have their own table. So, you would use a third table to associate departments to users. This gives a greater flexibility over the data. For example, if you need to change a department name, you just change one record in the Department table. The current design would require you to update all the association records because the department name is assigned to each user not a foreign key:

Here is an example structure of what I am talking about

Code: [Select]

Table: Users (One record for each user)
User_ID | User_Name
 1        Bob
 2        Jane
 3        Alex

Table: Departments (One record for each Dept)
Dept_ID | Dept_Name
  8       Music
  9       Business
 10       History

Table: User_Dept (Record for each User/Dept assoc)
User_ID | Dept_ID
 1         8
 1        10
 3         9
 2         8
You should be able to see that the user "Bob" is associated with both Music and History.

[TapeGun007]

Ok, just to recap, and just to make sure (for my sake) there is a one to many relationship between Table:Users -> Table:Departments and then a one to many relationship between Table:Departments -> Table:Sub_Departments.

I see what your are saying here, and it makes sense.  However, because I've never used a "3rd table", I'm not certain how to go from your example here to adding in that 3rd table.  How does the 3rd table tie into your example, mjdamato?

[Psycho]

Well, there are still some unanswered questions. Can a user be associated with a department BUT not associated with any sub-department? If the answer is no then you would need the following tables:
 - Users
 - Departments
 - Sub_Departments
 - Dept_SubDept (associate sub departments to departments)
 - User_Sub_Dept (associate users to sub departments)

In that example, since users are associates to sub-departments and sub-departments are associated with departments, you don't need a table to associate users to departments as it is inferred.

BUt, in my example in the previous post that was only about users and departments, here is what the query would look like to get a list of users and their associated departments using the three tables:

Code: [Select]

SELECT u.User_Name, d.Dept_Name

FROM Users u
JOIN User_Dept ud
  ON ud.User_ID = u.User_ID
JOIN Departments d
  ON ud.Dept_ID = d.Dept_ID
The results would look like this

Code: [Select]

User_Name | Dept_Name
======================
Bob         Music
Bob         History
Jane        Music
Alex        Business

[TapeGun007]

To answer your question:
1) Some users are not assigned to any departments
2) Some users are assigned to a Department but not a sub department
3) Some users are assigned to a Department that has no sub departments.
4) Sub Departments can be assigned to a Department before a user is assigned to it.

Now, because I'm no expert, I came up with, I *think* a better way of doing this whole thing.

Check out this table idea I have.  What if I just simplified the Departments table to include actual sub-departments and add a Parent_Dept_ID instead?

Table: Users         
  User_ID  User_First_Name   
  1             Bob   
  2             Jane   
  3             Alex   
         
Table: Departments         
  Dept_ID  Dept_Name    Parent_Dept_ID
  8             Music              Null
  9             Business         Null
  10           History            Null
  11           Muscian          8
  12           Choir               8
  13           Chorale           8
  14           Accounting      9

Table: User_Depts
  User_ID Dept_ID
  1             8
  1             10
  1             13
  3             9
  3             14

Now you can see that Bob is a part of Department Music, and "Sub Department" Chorale which is under the Music Department.  He is also a part of the History Department which has no sub departments.

Alex is a part of the Business Department and the Accounting sub department.

I think this table will actually suite my needs better and would be easier to manage with mySQL, don't you think?

[SchweppesAle]

No, it is completely different. You suggested adding two fields to the user table for department and sub-department. However, as per the requirements there is a many-to-one relatinship between users and departments/sub-departments. So, you need a secondary table to associate the users with 0 to many departments/sub-departments. That is database design 101, read up on database normalization. The suggestion I made was totally different. The current structure has users and departments tables (where the users are associated with departments via a foreign key in the departments table. However, this is not normalized as the department names are duplicitive data. The Departments should be unique records and have their own table. So, you would use a third table to associate departments to users. This gives a greater flexibility over the data. For example, if you need to change a department name, you just change one record in the Department table. The current design would require you to update all the association records because the department name is assigned to each user not a foreign key:

Here is an example structure of what I am talking about

Code: [Select]

Table: Users (One record for each user)
User_ID | User_Name
 1        Bob
 2        Jane
 3        Alex

Table: Departments (One record for each Dept)
Dept_ID | Dept_Name
  8       Music
  9       Business
 10       History

Table: User_Dept (Record for each User/Dept assoc)
User_ID | Dept_ID
 1         8
 1        10
 3         9
 2         8
You should be able to see that the user "Bob" is associated with both Music and History.

Hmmm....I think I understand what you're saying.  Dep/Sub_dep IDs don't belong in the User table since some entries won't share a relationship with either one.

So does this mean that 1 additional table should always be created whenever there's a "many to one" relationship between 2 separate tables?

Ex:
The User_Dept table from your example which relates User and Department with its own entry.

If that's the case then I think he's going to need 5 tables 

Users
Depart
Sub_Depart
Users_Depart
Users_Sub_Depart

[TapeGun007]

As far as I can tell, I actually did it in 3 tables, and I'm not replicating any information, thus wasting space.

[Psycho]

So does this mean that 1 additional table should always be created whenever there's a "many to one" relationship between 2 separate tables?

Ex:
The User_Dept table from your example which relates User and Department with its own entry.

If that's the case then I think he's going to need 5 tables 

Users
Depart
Sub_Depart
Users_Depart
Users_Sub_Depart

Right, if users can be assigned to a department, but not a sub-department, then you pretty much need separate associative tables. However, that is based upon the fact that departments and sub-departments are logically different objects. For example, if departments records need to store information such as department head, office number, phone, etc. and sub-department records need to store totally different information you need those separate tables.

However, based upon TapeGun007's last post that is apparently not the case and he will be storing the exact same information for departments and subdepartments, so his approach of one table for both should work as well.

Here is the query I would use with a single table for departments and sub departments as illustrated above:

Code: [Select]

SELECT u.User_Name  as name,
       d.Dept_Name  as department,
       dp.Dept_Name as parent

FROM Users u
JOIN User_Dept ud
  ON ud.User_ID = u.User_ID
JOIN Departments d
  ON d.Dept_ID = ud.Dept_ID
JOIN Departments dp
  ON dp.Dept_ID = d.Parent_Dept_ID


As far as I can tell, I actually did it in 3 tables, and I'm not replicating any information, thus wasting space.

It all depends on whether departments and sub-departments should be logically different entities. If you are capturing all the same information for both, then the three tables will suffice.

[TapeGun007]

Thank you mjdamato, I learned a lot from looking up your SQL commands and reading about them.  It was a great guide in helping learn SQL syntax than just FROM, WHERE, and ORDER BY. Hehehe.