3

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.

  • What happens when the business invents a 3rd NeedSourceType? – Dan Pichelman Oct 7 '15 at 19:37
  • Good question. I think the 2 need types would cover most needs for now, but you're right, there could be another added at some point. So the current one table design is more flexible from that standpoint. – Chad Richardson Oct 7 '15 at 20:11
  • Am I taking it correctly that NeedSourceId is a reference to one of two other tables (either Ship Order or Work Order) depending on NeedSourceType? – Erik Eidt Oct 7 '15 at 22:05
  • @ErikEidt yes...sorry for not being more clear on that. – Chad Richardson Oct 7 '15 at 23:19
  • Ok, so that is an interesting design, but to be more true to the relational model you would use a foreign key here, which implies that these two types would have (at least) a common table. Since you're looking at some polymorphism with that table (two types, with some commonality) a number of alternatives can be used (e.g. fat table, probably the best). – Erik Eidt Oct 7 '15 at 23:52
2

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.

  • I would prescribe "exactly one of ShipOrderId and WorkOrderId must be non-null" rather than "at most one of" – Caleth Oct 8 '15 at 8:55
  • @Caleth uh, you are right. I will fix this. I was thinking of inheritance relationships in ORM, where a class may have one or more subclasses, but the class of a particular instance may happen to be of the base class, not of any derived class. – Mike Nakis Oct 8 '15 at 9:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.