Solved: Db table Relationships in SQL Diagram

Create Account

Microsoft SQL Server

Questions

Followers

Top Experts

Niall Gallagher🇮🇪

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

Zero AI Policy

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

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?

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

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

Get a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

SOLUTION

slubek🇵🇱

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

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

Signing up is free and takes 30 seconds. No credit card required.

Create Account

Microsoft SQL Server

Questions

Followers

Top Experts

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.