Solved

Db table Relationships in SQL Diagram

Posted on 2014-10-28
9
145 Views
Last Modified: 2014-10-28
Hi all I hope you can help or put me in the right direction.

I have 4 tables
Depts:
 Dept_ID int PK,
 Dept_Name varchar(25)

Dept_To_Dept
Dept_ID int PK
Dept_Group_ ID int PK

Position:
Dept_Group_ID Int PK
Position_ID Int PK

Employee: (Lots of columns but these are the only 2 relevant)
Dept_ID int 
Position_ID int

Open in new window


Each employee has a dept ID and a position ID.  
The employee.Dept ID FK to Depts.Dept_ID.
Depts_To_Dept.Dept_id  FK to DEPTS.Dept_ID

Now I am trying to get the Position_ID from employee and Dept_Group_ID from Dept_To_Dept to join to Dept_ID and Position_ID in Position table but can't get it to work as Dept_Group_ID is not unique or Position_ID is not unique but they are unique together.
I know this might not make sense so please ask any questions you want so I can try an clarify.

Thanks in advance
0
Comment
Question by:Niall292
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40408819
You can create a composite PK:
ALTER TABLE Position
ADD CONSTRAINT PK_Position 
PRIMARY KEY (Dept_Group_ID , Position_ID) 

Open in new window

0
 

Author Comment

by:Niall292
ID: 40408842
Thank you for responding so quick but how can I get the Employee.Position_ID to correspond with it. When I try to set it up through the SQL Diagram  I get an error because position_ID is not unique in any table and Dept_Group_ID is not in Employee table.
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40408850
I'm not used to the SQL Diagrams. I create everything by script.
Can you post here a screenshot with the error?
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:Niall292
ID: 40408940
The error I am getting with these is slightly different it is because I can't use the Dept_ID in the Employee table because it doesn't match the Dept_id (Dept_Group_ID, I changed their names in my question to make it easier to understand, I apologise if this is confusing now when you see the diagram) column in the Position table.
in THe second Diagram you can see where I tried to build a junction table but I still can't get Position_ID from Employee to mix with it as each dept uses the same position IDs but its the Dep_ID + Position_ID makes it unique
Diagram1.jpg
Diagram2.jpg
0
 
LVL 7

Expert Comment

by:slubek
ID: 40409320
Define composite unique indexes for both tables (Employee and POISTION_2) and then define relationship:
Relations.png
0
 

Author Comment

by:Niall292
ID: 40409608
Slobek,
Thank you for you response but the naming convention I'm stuck with seems like it put you off.

Position_2. Dept_ID matched Dept_To_Dept.Dept,  and Employee.Dept_ID matched both Dept_Dept_id and Dept_To_Dept.Dept_ID
So there is no direct way to match Dept_ID from Employee to Position_2

It would make more sense if Position_2.Dept_ID was called Position_2.Dept_Group and Dept_To_Dept.Dept was Dept_To_Dept.Dept_Group
0
 
LVL 7

Assisted Solution

by:slubek
slubek earned 500 total points
ID: 40409649
Well, there are so many 'Depts' here... :^)
I think one should avoid such confusing naming conventions.

OK, let me check if I understand you correctly. Does it make sense for you? :
select p2.*, e.*
from position2 p2
  inner join Dept_To_Dept dtd on p2.Dept_ID=dtd.Dept
  inner join Employee e on dtd.Dept_ID=e.Dept_ID

Open in new window

0
 

Author Comment

by:Niall292
ID: 40409673
Yes, that makes perfect sense

Now is there a way I can show that relationship without showing the Query.
I normally do/did everything in stored procedures (I am a C# developer) but my boss wanted me to show the relationship in a SQL Diagram (which I have never done before)

If it is not possible I will try to show the query and hope he understands.
0
 
LVL 7

Accepted Solution

by:
slubek earned 500 total points
ID: 40409713
What you are trying to show is called transitive dependency. I don't think there is a way to show it on diagrams in MS SQL Server Management Studio without using middle table (Dept_To_Dept). And without breaking database, of course, as diagrams here are strictly connected with db structure.

You can show that on another software for creating ERD diagrams, but I don't think its worthy.

Show your boss a diagram with that three tables from my query (and with relations defined by INNER JOINs) - if he knows anything about databases, he should accept it.
0

Featured Post

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question