Solved

Db table Relationships in SQL Diagram

Posted on 2014-10-28
9
139 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 46

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 46

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

920 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

16 Experts available now in Live!

Get 1:1 Help Now