Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 159
  • Last Modified:

Db table Relationships in SQL Diagram

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
Niall292
Asked:
Niall292
  • 4
  • 3
  • 2
2 Solutions
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Niall292Author Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm not used to the SQL Diagrams. I create everything by script.
Can you post here a screenshot with the error?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Niall292Author Commented:
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
 
slubekCommented:
Define composite unique indexes for both tables (Employee and POISTION_2) and then define relationship:
Relations.png
0
 
Niall292Author Commented:
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
 
slubekCommented:
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
 
Niall292Author Commented:
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
 
slubekCommented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now