I want to explain one specific use case here, because the implementation of said use case is relevant for you to consider.
Entity Framework allows you to model inheritance in your database. What you're dealing with is very similar to an inheritance pattern. I could refactor your structure as code classes, which I'm going to since it helps study the use case I have in mind.
Your suggestion is something along the lines of:
public class Cost
{
public Guid Id { get; set; }
public decimal Price { get; set; }
public int Quantity { get; set; }
public Guid ProjectId { get; set; }
public Guid? ProductId { get; set; } // Either this is null
public Guid? ServiceId { get; set; } // or this is null
}
However, in code you would avoid this, instead using inheritance to ensure that you only add the field where it will actually be used:
public class Cost
{
public Guid Id { get; set; }
public decimal Price { get; set; }
public int Quantity { get; set; }
public Guid ProjectId { get; set; }
}
public class ProductCost : Cost
{
// Should never be null since this is always a product cost!
public Guid ProductId { get; set; }
}
public class ServiceCost : Cost
{
// Should never be null since this is always a service cost!
public Guid ServiceId { get; set; }
}
Now you don't have to deal with fields that are "obviously" null; which saves you from having to constantly check for null to figure out what kind of cost this is.
If the above makes sense to you, the database equivalent of this will seem very familiar. When presented with these inherited classes, Entity Framework will generate tables that represent this relationship. However, there are a few different approaches here. All of them make sense in their own way.
More information here.
Table-per-hierarchy (TPH)
This is essentially what you're suggesting. You merge all the fields into a single table and set them to null when you're not using them. This yields the fastest querying if you intend to query across all costs, but it comes at the cost of significant waste of database storage.
Resulting tables: Cost
Table-per-type (TPT)
This represents the three classes as three separate tables. The PK for the subtypes is actually reusing the PK from the base type, to ensure that the identities work correctly. Each table contains exactly the fields that its related class contains.
This is highly efficient from an storage efficiency perspective as no column is ever left unused; but it makes it less efficient to query across all costs since you now have to deal with table joins. However, the PK uniqueness space is still shared between the subtypes, which means that there will never be a product cost and a service cost with the same PK.
Even if you only wish to fetch e.g. product costs, your query must use a table join because it must get data from both the Cost and ProductCost tables. While this join can be optimized; it's still a non-zero cost to pay.
Resulting tables: Cost, ProductCost, ServiceCost
Table-per-concrete-type (TBC)
This is a variation of TPT which can only be used when the base type is abstract and cannot be instantiated directly (i.e. you cannot have a cost which is neither a product cost nor a service cost). In this case, the base type does not get a table, and its columns are added to the subtypes (which are "concrete", i.e. non-abstract).
The benefit here (over TPT) is that when you are only querying e.g. product costs; you do not have to rely on a table join. All the information is in the ProductCost table. The drawback here (over TPT) is that the PK uniqueness is no longer shared across all costs, because there is no longer a shared table between all the cost types.
Other than that, the same pros and cons of TPT apply to TPC.
Resulting tables: ProductCost, ServiceCost
As you can see, there are several ways to represent this inheritance relationship. The choice between them is mostly a choice of which database behavior you wish to optimize, so I suggest you review the options and decide which part you want to be optimized the most.
Just to be clear here, I'm not saying you have to use Entity Framework; but you can take inspiration from the database structures that Entity Framework utilizes to model code inheritance.
