Two tables with similar structure and referential integrity or one table

Question

Overview

My question revolves around balancing database and code simplicity using 2 options to design around the following requirement.

Background Facts

• The DB: Sql Server
• The Code: ASP.NET MVC C# using EntityFramework

I have a table, AllocationNeed (see below).

/****** Object:  Table [dbo].[AllocationNeed]    Script Date: 10/7/2015 12:39:43 PM ******/
CREATE TABLE [dbo].[AllocationNeed](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FacilityId] [int] NOT NULL,
    [WorkReleaseHeaderId] [int] NULL,
    [NeedSourceId] [int] NOT NULL,
    [NeedSourceType] [nvarchar](50) NOT NULL,
    [ItemId] [int] NOT NULL,
    [QtyNeeded] [decimal](18, 4) NOT NULL,
    [CreatedById] [int] NOT NULL,
    [CreatedOn] [datetime] NOT NULL,
    [ModifiedById] [int] NOT NULL,
    [ModifiedOn] [datetime] NOT NULL

There are at least 2 sources of inventory needs and today am using a NeedSourceType and NeedSourceId to hold that information in this single table.

In this case a NeedSourceType is either a "Ship Order" and a "Work Order". These both are also represented in other tables as OrderHeader and WorkOrderHeader respectively. NeedSourceId is the respective foreign key values of OrderHeader.Id or WorkOrderHeader.Id.

There is also a related child table (many to 1 AllocationNeed) AllocatedContainers, which has a foreign key back to AllocationNeed.Id. In the near future, this table could also have the same issue in that we will need to add the concept of AllocatedLocations in addition to AllocatedContainers, so again, we could have this be 2 tables or one.

Approach Options Pros and Cons

Approach: One Table

Pros:

1. my code is a bit cleaner in that I normally need to run aggregates on the total combined inventory need, so no need to do a join if these were two tables
2. Code is simpler for writing and reading to this table instead of 2 or more tables
3. doesn't add additional tables to DB

Cons:

1. I often need to get attributes of the associated Ship and Work Orders, so I need to do joins in my code instead of just accessing AllocationNeed.OrderHeader.ShipDate
2. There is no referential integrity
3. Can't use cascading deletes so code is more complex to delete records.

Approach: Two Tables

Basically the opposite Pros and Cons to the one table approach.

Looking for thoughts or input on this design dilemma.

Answer 1

On your main tables on which you add/remove records, always do things in perfect relational tradition. "Referential integrity is not enforceable" means "it is not relational", and "it is not relational" should always automatically mean "it is unacceptable". If you do not do it this way, you will sooner or later regret it bitterly.

The type of either-or relationship you are trying to model is best (most relationally) described with multiple foreign keys, (in your case two,) of which exactly one will be non-null in any given row. So, if I understand your scenario correctly, you would lose the NeedSourceType and NeedSourceId columns, and replace them with a ShipOrderId and a WorkOrderId.

If you need to have a simpler, read-only view of things, (such as for running aggregates on the total combined inventory,) create database views which are basically pseudo-tables defined as queries.

If you have more heavy data mining needs, then create additional sets of tables that contain the same information but in a denormalized form (not strictly relational) in order to facilitate easier querying. But these tables should be used only for querying, never for updating and deleting.