CODEWITHSUNDEEP
sql-serversql-server-2008database-designdata-modeling
CrassHoppr
CrassHoppr

Reputation: 43

Database schema consistency issue

Part of my database schema involves the entities:

Jobs
Agencies
Agents
and relation JobAgent

The database will be SQL Server 2008

Here is my schema:

enter image description here

My problem is that Jobs.agencyid must always be equal to Agents.agencyid when related through JobAgent. If Jobs.agencyid were to be updated to a new agency, The Agents would then belong to a different Agency than the Job.

What would be the best way to redesign my schema to avoid relying on triggers or application code to ensure this consistency?

Upvotes: 2

Views: 336

Answers (4)

Richard Inglis
Richard Inglis

Reputation: 5958

The problem is that if a job moves from one agency to another (as you say, if Jobs.agencyid were to be updated...) then the corresponding records in JobAgent become meaningless: those agents can't be attached to a job that's no longer with their agency, so the JobAgent records connecting them to the jobs should therefore be deleted...

One way to enforce this is to add a JobAgent.agencyid field, and make it a foreign key on Jobs.agencyid, with ON UPDATE RESTRICT to force (manual) deletion of the relevant JobAgent records before Jobs.agencyid can be changed.

Edit: the other issue, which I hadn't really considered, is that when you first associate a job to an agent (ie create a new JobAgent record) you need to ensure they both belong to the same agency... for this, I think OMG's solution works best - I'm happy to defer to the better answer.

OMG also raises the question of how to handle updates: you can either

  1. Change the Jobs.agencyid field and delete (by hand) all associated JobAgent records: in this case the old agents no longer work on this job, and you can assign someone from the new agency to work on it.

  2. Change the Jobs.agencyid field and also change all associated JobAgent records (ie all those agents move with the job to the new agency) - but this is very messy, because those agents will also be associated with other jobs that are still with the original agency.

  3. As OMG suggests, make a new Jobs record and mark the old one as defunct (for later deletion).

  4. As above but keep the defunct Jobs record to preserve historical information.

Whether you choose 3 or 4 depends a bit on what your system is for: do you just want to maintain the current state of who-has-which-jobs? or do you need to keep some kind of history, for example if there's billing records attached to the job... that info needs to stay associated with the original agency (but this is all outside the scope of your original question).

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332571

AGENCIES

  • agency_id (pk)

JOBS

  • job_id (pk)
  • agency_id (fk to AGENCIES.agency_id)

AGENTS

  • agent_id (pk)
  • agency_id (fk to AGENCIES.agency_id)

JOBAGENT

  • job_id
    • fk to JOBS.job_id
  • agent_id
    • fk to AGENTS.agent_id
  • agency_id
    • fk to JOB.agency_id
    • fk to AGENTS.agency_id

You can define more than one foreign key constraint to a column - it just means that the value in JOBAGENT has to satisfy BOTH foreign key constraints to be allowed. But you'll have fun if you ever want to update jobs to a different agency... ;) SQL Server supports composite foreign keys: http://msdn.microsoft.com/en-us/library/ms175464.aspx

Update Regarding Updating

You have two choices -

  • Perform by hand, because ON UPDATE CASCADE etc won't handle agency and agent updates without using triggers
  • Have a status column in JOB, so you can cancel a job in order to recreate the job with the new supporting records (Agent, jobagent, etc). Further cleanup can be automated, based on job status if you desire

Upvotes: 1

sergiol
sergiol

Reputation: 4335

Does the following scheme answer your question?

    Jobs      Agents     Agencies
      ^          ^          ^
      |          |          |
       \         |         /
        \        |        /
           AgentiatedJob

Normally, I have a single-field primary key for every table, because it is easier to match a registry on a table and to refer it on tables below. So following this approach the AgientiatedJob would have at least the fields:

  • AgentiatedJobId
  • JobId
  • AgentId
  • AgencyId

Upvotes: 0

Anthony Accioly
Anthony Accioly

Reputation: 22461

You could use ON UPDATE CASCADE with the foreign keys. See this Wikipedia Page. Or maybe, if agencyid is something that you expect to be mutable, you can have a unique constraint for it and use some other meaningless field for the agency id (say, an auto-increment column).

Upvotes: 0

Related Questions