I want to create a database that I can assign a job to a person based on
their skills. Some people have more than one skill. Some jobs need more than
one person. So an example is: Joe is a carpenter and plumber. Peter is a
plumber and electrician.
There is a Job that needs a carpenter and a electrician. I want to be able
to choose Joe and Peter and if the job only needs 2 people then that job is
filled.
Consider this structure (ANSI-92 Query Mode SQL DDL syntax):
CREATE TABLE Professions
(
profession_name VARCHAR(20) NOT NULL UNIQUE
)
;
CREATE TABLE Professionals
(
person_name VARCHAR(35) NOT NULL,
profession_name VARCHAR(20) NOT NULL
REFERENCES Professions (profession_name)
ON DELETE CASCADE
ON UPDATE CASCADE,
UNIQUE (profession_name, person_name)
)
;
CREATE TABLE Jobs
(
job_number CHAR(10) NOT NULL UNIQUE
)
;
CREATE TABLE JobsProfessonalsRequirements
(
job_number CHAR(10) NOT NULL
REFERENCES Jobs (job_number)
ON DELETE CASCADE
ON UPDATE NO ACTION,
profession_requirement_name VARCHAR(20) NOT NULL
REFERENCES Professions (profession_name)
ON DELETE CASCADE
ON UPDATE CASCADE,
UNIQUE (profession_requirement_name, job_number),
profession_required_amount INTEGER NOT NULL,
CONSTRAINT profession_required_amount_must_be_positive
CHECK (profession_required_amount > 0),
UNIQUE (profession_requirement_name, job_number,
profession_required_amount)
)
;
CREATE TABLE JobsProfessonalsRecruitment
(
job_number CHAR(10) NOT NULL,
profession_requirement_name VARCHAR(20) NOT NULL,
UNIQUE (job_number, profession_requirement_name),
profession_required_amount INTEGER NOT NULL,
FOREIGN KEY (profession_requirement_name, job_number,
profession_required_amount)
REFERENCES JobsProfessonalsRequirements
(profession_requirement_name, job_number,
profession_required_amount)
ON DELETE CASCADE
ON UPDATE CASCADE,
sequence_number INTEGER NOT NULL,
CONSTRAINT professionals_tally_exceeds_profession_required_amount
CHECK (sequence_number BETWEEN 1 AND
profession_required_amount),
UNIQUE (job_number, profession_requirement_name, sequence_number),
professional_person_name VARCHAR(35) NOT NULL,
profession_name VARCHAR(20) NOT NULL,
CONSTRAINT profession_name__requirements_must_professionals
CHECK (profession_requirement_name = profession_name),
FOREIGN KEY (profession_name, professional_person_name)
REFERENCES Professionals (profession_name, person_name)
ON DELETE CASCADE
ON UPDATE CASCADE,
UNIQUE (job_number, profession_requirement_name,
professional_person_name)
)
;
The above CHECK constraints can be implemented using column- and row-
level Validation Rules; I'm using SQL DDL here for my own convenience
<g>.
Note the JobsProfessonalsRecruitment table has more columns than is
ideal. However, I'd stop short of saying they were redundant because
their purpose is to be able to use DRI (declarative referential
integrity or, in the Access parlance, Relationships with RI enforced)
to ensure data is consistent with business rules, which is far more
important than ideals IMO.
FWIW I don't think in this case there is scope for using table-level
CHECK constraints. A CHECK constraint on the tally of recruited
professionals would fail to be checked if and when the requirement
amount was changed (whereas DRI prevents or cascades it to good
effect). A CHECK constraint to test the profession in the recruitment
table would fail to get checked if and when a professional loses a
profession (again, DRI to the rescue).
PS Eagle-eyed SQL DLL speakers may have noticed I have multiple
cascade paths (and the knowledgeable readers will be aware that Jet is
an optimistic system in that it allows it, creating a race situations
where the loser gets to overwrite the values last). I don't think they
will pose a problem due to
CHECK (profession_requirement_name = profession_name)
but in this scenario perhaps NO ACTION is the better advice.
Jamie.
--
