Solved

Db table Relationships in SQL Diagram

Posted on 2014-10-28
9
143 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
  • 4
  • 3
  • 2
9 Comments
 
LVL 49

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 49

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

733 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