asked on

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

Vitor Montalvão

You can create a composite PK:

ALTER TABLE Position
ADD CONSTRAINT PK_Position 
PRIMARY KEY (Dept_Group_ID , Position_ID)

Open in new window

Niall Gallagher

ASKER

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.

Vitor Montalvão

I'm not used to the SQL Diagrams. I create everything by script.
Can you post here a screenshot with the error?

Niall Gallagher

ASKER

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

slubek

Define composite unique indexes for both tables (Employee and POISTION_2) and then define relationship:

Niall Gallagher

ASKER

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

SOLUTION

slubek

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Niall Gallagher

ASKER

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.

ASKER CERTIFIED SOLUTION

slubek

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial