score:1

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.

score:0

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.

score:0

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;

score:0

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.

score:0

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.


Related Articles