Solved

Db table Relationships in SQL Diagram

Posted on 2014-10-28
9
136 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 45

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 45

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
 

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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
IN with @variable 5 20
SQL JOIN + SUBQUERY? 3 15
Merge Statement 3 9
Join vs where 2 11
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now