Forum Discussion
Multi-level constraints
I'm tracking workers in the construction industry.
The industry is classified into trades - Laborer, Operating Engineer, Carpenter, etc.
Each worker can have one or more trades.
Within each trade there are specializations or skills.
For example, an Operating Engineer could have the skills to use one or more specific machines (digger, bobcat, crane, etc).
I need to track each worker's trades and each worker's specialization, if any. Which trade(s) a worker has will determine the pool of specializations/skills he/she can have.
How can I create restrictions so that the worker can only have the skills that fall under his/her trade?
Below is a simplified sample of my schema:
| Worker |
| Name |
| John |
| Barbara |
| Trade |
| Name |
| Laborer |
| Operating Engineer |
| WorkerTradeJunction | |
| Worker | Trade |
| John | Laborer |
| Barbara | Operating Engineer |
| Skill | |
| Skill | Trade |
| Pile Driver | Laborer |
| Digger | Operating Engineer |
| WorkerSkillJunction | |
| Worker | Skill |
| John | Pile Driver |
| Barbara | Digger |
Again: How can I make it so that Barbara (Operating Eng) cannot have the skill of Pile Driver and John (Laborer) cannot have the skill of Digger?
Thank you!
4 Replies
- I am not able to paste the image. What I can do is share the text of the tables with foreign keys.
CREATE TABLE [dbo].[SkillName](
[SkillName] [varchar](50) NOT NULL,
CONSTRAINT [PK_SkillName] PRIMARY KEY CLUSTERED
(
[SkillName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Trade](
[TradeName] [varchar](50) NOT NULL,
[SkillName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Trade] PRIMARY KEY CLUSTERED
(
[TradeName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Worker](
[WorkerName] [varchar](50) NOT NULL,
[TradeName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Worker] PRIMARY KEY CLUSTERED
(
[WorkerName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Trade] WITH CHECK ADD CONSTRAINT [FK_Trade_SkillName] FOREIGN KEY([SkillName])
REFERENCES [dbo].[SkillName] ([SkillName])
GO
ALTER TABLE [dbo].[Trade] CHECK CONSTRAINT [FK_Trade_SkillName]
GO
ALTER TABLE [dbo].[Worker] WITH CHECK ADD CONSTRAINT [FK_Worker_Trade] FOREIGN KEY([TradeName])
REFERENCES [dbo].[Trade] ([TradeName])
GO
ALTER TABLE [dbo].[Worker] CHECK CONSTRAINT [FK_Worker_Trade]
GOMCarr10, that doesn't solve my issue.
I've got three many-to-many relationships, which are Worker-Trade, Trade-Skill, and Worker-Skill.
The rules are the following.
A worker cannot have one or more trades.
A worker zero or more skills for each trade.
A worker can only have a skill if he has the trade to which that skill belongs.
The way I understood it, the scheme you provided would work if workers could only have one trade and if each trade only could have one skill.
Hi, and thanks for taking this on!
Firstly, can you kindly paste the diagram into the body of the message? My employer has a strict policy about opening files.
Secondly, to clarify, workers have one or more trades, and trades can have zero specific skills/specifications or can have one or more.
So Mike (worker) could be a carpenter (trade) with no special skills/specifications.
Bob (worker) could be a laborer (trade) who knows how to use a pile driver (skill) and an operator (trade) who knows how to use a digger (skill) and a crane (skill)
Barbara (worker) could be an electrician (trade) with a master certificate (skill)
I hope that makes it more clear.
