Foreign keys to both parent and grand parent

Question

I have 3 database tables named "Projects", "Contracts" and "Incidents". The design is for a project based maintenance system. Customers are able to establish contracts on a project for maintenance of various installations. Additionally, isolated incidents that may or or may not be related to a contract on a project, should be reportable, like for example defective installations.

Projects has a 1 to many relationship with Contracts (each Project can have multiple Contracts, or none). A record from Incidents must ultimately be resolvable to a Project, but does not always require a contract to be present. In some cases it may be possible that a project does not have any contracts, but it should be able to have Incidents nevertheless.

Our database designer has proposed that Incidents holds a foreign key to both Projects and Contracts. In effect this is a relationship which has separate keys to parents and grandparents, to allow for the absence of a parent record. The alternative is to create a "dummy" Contract. Neither solution has my preference.

To make things worse, a Contract also references a "Debtor" from another table. So in the absence of a Contract, an Incident should also be able to reference a Debtor.

I can't help feeling that the proposed approach is a violation of all normal forms and has a potential to create future problems, including being a maintenance problem, so I am looking for an alternative solution that is able to maintain integrity across the tables. In addition, is anyone familiar with further problems this approach may cause?

For what it's worth, I am the developer that is responsible for writing the application that will work with this database. The project is to be created in WPF with LINQ over SQL. One requirement is that it should be able to query a Project record for all it's Incidents, including the ones that are referenced via Contracts.

I have looked for similar questions on SO, and although there are many dealing with grandparent keys, none of them seem to match my problem.

Answer 1

I would use a trigger to enforce integrity. Now, a ProjectID is always required within an Incident. When a ContractID is added as foreign key, the trigger would check if the ProjectID of the inserted ContractID matches with the already inserted ProjectID, else don't allow insert. This always ensures you don't end up with corrupt relationships. Also, this makes it a lot easier to generate reports of all Incidents within a Project or all Incidents within a specific Contract.

Answer 2

I think introducing a dummy contract into the system is the way to go. What I normally do is I would make a dummy contract, marking it as deleted (so that it isn't picked up in any of your queries). Then I would create another project in your solution to hold constants. Then I'd have a line like this:

public static readonly int DummyContractId = 25; // Or whatever the ID is of your dummy contract ID.

Now you can construct queries in your code that either exclude or include incidents that have a dummy contract.

Answer 3

I am not a fan at all of "dummy" data. If an incident can only relate to one contract, or no contracts, then I would adopt an approach like this:

CREATE TABLE dbo.Project 
(
        ProjectID INT IDENTITY,
        Filler CHAR(1) NULL,
    CONSTRAINT PK_Project__ProjectID PRIMARY KEY (ProjectID)
);

CREATE TABLE dbo.Contract 
(
        ContractID INT IDENTITY,
        ProjectID INT NOT NULL,
        Filler CHAR(1) NULL,
    CONSTRAINT PK_Contract__ContractID PRIMARY KEY (ContractID),
    CONSTRAINT FK_Contract__ProjectID FOREIGN KEY (ProjectID) REFERENCES dbo.Project (ProjectID),
    CONSTRAINT UQ_Contract__ContractID_ProjectID UNIQUE (ContractID, ProjectID)
);

CREATE TABLE dbo.Incident
(
        IncidentID INT IDENTITY,
        ProjectID INT NOT NULL,
        ContractID INT NULL,
        Filler CHAR(1) NULL,
    CONSTRAINT PK_Incident__IncidentID PRIMARY KEY (IncidentID),
    CONSTRAINT FK_Incident__ProjectID FOREIGN KEY (ProjectID) REFERENCES dbo.Project (ProjectID),
    CONSTRAINT FK_Incident__ContractID FOREIGN KEY (ContractID, ProjectID) REFERENCES dbo.Contract (ContractID, ProjectID)
);

-- CREATE TWO DUMMY PROJECTS
INSERT dbo.Project DEFAULT VALUES;
INSERT dbo.Project DEFAULT VALUES;

-- ADD A CONTRACT TWO EACH
INSERT dbo.Contract (ProjectID)
SELECT  ProjectID
FROM    Project;

-- ADD AN INCIDENT TO EACH WITH NO CONTRACT
INSERT dbo.Incident (ProjectID)
SELECT  ProjectID
FROM    Project;

-- ADD A VALID INCIDENT TO EACH CONTRACT
INSERT dbo.Incident (ContractID, ProjectID)
SELECT  ContractID, ProjectID
FROM     dbo.Contract;

-- TRY AND ADD INVALID CONTRACT TO FIRST PROJECT
INSERT dbo.Incident (ContractID, ProjectID)
SELECT  c.ContractID, p.ProjectID
FROM    dbo.Project AS p
        CROSS JOIN dbo.Contract AS c
WHERE   c.ProjectID != p.ProjectID;

This will fail with the error:

>The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Incident__ContractID". The conflict occurred in database "TestDB", table "dbo.Contract".

A foreign key can refer to the unique constraint on Contract, which allows you to enforce integrity in dbo.Incident, i.e. you cannot enter a project that does not correctly map to the contract being entered. The only real downfall of the scenario is that you are duplicating ProjectID when ContractID is populated, but I don't think this is a massive issue. Certainly (in my opinion) less of an issue than dummy data.

It is then very simple to identify dummy contracts:

SELECT  *
FROM    dbo.Incident
WHERE   ContractID IS NULL;

Answer 4

I have no preference or experience either. At first glance I like the idea of a 'dummy' contract. If you do so I suggest to add a specific column to the contract so you can easily see if it is a dummy or a real contract.

A single dummy contract can hold all contract-less-incidents. The risk of the dummy contract comes when you start using fields of the dummy contract (like Debtor). If there is no contract, is the Debtor the same for all incidents of the same project ? If not this would mean you will end up with multiple dummy contracts (one per Debtor). Maybe in the future you have other fields which ultimately lead to a dummy contract per incident.

I don't know your business but these contract-less-incidents can become very dangerous for your design.

Another approach is to use the contract as a blueprint/template for incidents. In this case you have the debtorId, contractId and projectId (...) on incident level. When the incident is created and linked to a contract, some of the contract information is copied to the incident. This provides the most flexibility at incident level which is something you need for incidents without contracts. You can decide to make these incident fields readonly and synchronized if there is a related contract.

Answer 5

Here is a simpler approach. Have a dummy contract for each project that is used for incidents that have no formal contract. This contract would always be used for resolving the project.

This simplifies the database design, but it does introduce other problems. For instance, to find incidents with no contract, you wouldn't look for NULL in the contract column. You would look for "not really a contract" in the contract table. Depending on the circumstances, this could be a more elegant solution. This also solves the problem with Debtor.

This does bring up another issue, which is incidents that might be on multiple contracts. In fact, you might end up heading in a direction where you need to support yet-another-table that is an n-m mapping between incidents and projects.