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
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.
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.
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
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;
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.
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.
- Foreign keys to both parent and grand parent
- select rows that do not have any foreign keys linked
- Multiple foreign keys to the same table
- LINQ Source Code Available
- Linq to 3 tables with no foreign keys
- .NET 4 Code Contracts: "requires unproven: source != null"
- How to return both parent and child using LINQ against 1 table
- Linq2Sql: Can I create entities with foreign key relationships without a primary key in both tables?
- linq to sql Association problem with foreign keys
- LINQ: Given A List of Objects, Create a Dictionary with child objects as keys and Parent Objects as Value
- Create LINQ Association without Foreign Keys
- creating Linq to sqlite dbml from DbLinq source code
- Deleting records in LINQ with foreign keys - invalid cast exception
- Selecting both parent and child items when querying child properties
- Need a LINQ code example to link two tables that have no foreign key
- How can I fetch child entities as DTO in parent using reusable queries/Expression's with EF code first?
- Entity Framework loading foreign keys with HasOptional
- most efficient Entity Framework Code First method of flattening / projecting parent entity with specific child
- Multiple layers of foreign keys in LINQ query
- Why SELECT N + 1 with no foreign keys and LINQ?
- EF Code First: Foreign Key With Where Clause?
- How to write an efficient LINQ query when searching by both parent and child entity fields
- Find missing foreign keys
- Using only Linq or Lambda, how do I combine both pieces of code to return List<Entity> e?
- source code for LINQ 101 samples
- LINQ GroupBy and project to type with both a list of its keys and result of ToDictionary
- SQL code to join to, and sum data in, a table referenced by comma delimited keys
- How to filtering related entities but with no foreign keys
- Entity Framework - Retrieve different values according to foreign keys
- Foreign keys which does not exist
- Combine two LINQ queries?
- Nested group by based on flatten record set
- Distinct() to return List<> returning Duplicates
- The type of one of the expressions in the join clause is incorrect when the types are the same
- Why is LINQ to SQL entity association creating a new (duplicate) row when inserting a new record?
- Linq performance poor
- Convert LINQ written in VB.NET to C#
- LINQ Join query problem
- LINQ not working for strings in Xamarin?
- How to [group by] the datatable and put the result in another datatable?
- How to look at sql generated by EntityFramework when Count is used
- C# & LINQ, Select two (consecutive) items at once
- How to make C# method generic so it could be used by different classes
- Using Where clause in Group Join
- Splitting a list of items evenly between x number of smaller lists
- can I have a nested lambda expression stored in a Dictionary?
- Query rows that the datatable field contain any item in the list<string>
- How to get distinct items from Microsoft.Office.Interop.PowerPoint.Hyperlinks
- Linq logic in the select statement
- LINQ Filtering - Optimization