My question revolves around balancing database and code simplicity using 2 options to design around the following requirement.
- 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
- 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
- Code is simpler for writing and reading to this table instead of 2 or more tables
- doesn't add additional tables to DB
- 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
- There is no referential integrity
- 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.