Hello all, I have a database design question related to order items and purchase orders items. I have a situation where I currently have a one to one relation between the order item (which I call BuildItem) and the PurchaseOrderItem. This one to one relation allows me to do a couple of important things like when looking at the order items grid it's easy to show what order items are on which purchase order and other info. Then from the purchase order item side when a cost is updated on a purchase order item it's easy to go and updated the cost of the related order item when the save occurs.

The problem I now have is that the fk constraint from BuildItem.ItemID to PurchaseOrderItem.ItemID is making it impossible to add purchase order items to a purchase order which don't exist as a build item. In the end it's SQL Server which is making this hard because I would like to just make the PurchaseOrderItem.ItemID a nullable unique constraint but SQL Server won't allow this since a null is considered to be a unique value. I have ready some articles, blogs and other forum posts and some say if this is a problem then my design is bad. So I am now asking for help. Does anyone know what might be a good solution to this problem?

Here is the current DDL: The second to last constraint is the one to one relation between PurchaseOrderItem and BuildItem that I need but is causing my problem.

CREATE TABLE [Build] ( 
    [BuildID] int identity(1,1)  NOT NULL,
    [CustomerID] int,
    [SalesPersonID] int,
    [PartSetID] int,
    [AdjustedGrams] decimal(7,2) DEFAULT (0) NOT NULL,
    [WheelBuildCost] smallmoney DEFAULT (0) NOT NULL,
    [LaborCost] smallmoney DEFAULT (0) NOT NULL,
    [WebQoute] smallmoney DEFAULT (0) NOT NULL,
    [BuildName] nvarchar(50) DEFAULT ('') NOT NULL,
    [TimeToCall] nvarchar(20) DEFAULT ('') NOT NULL,
    [PhoneToCall] nvarchar(20) DEFAULT ('') NOT NULL,
    [CustomerNotes] nvarchar(500) DEFAULT ('') NOT NULL,
    [WSNotes] varchar(500) DEFAULT ('') NOT NULL,
    [ContactedDate] smalldatetime,
    [ImageName] varchar(50) DEFAULT ('') NOT NULL,
    [CreateDate] datetime DEFAULT (getdate()) NOT NULL,
    [UpdateDate] datetime DEFAULT (getdate()) NOT NULL,
    [Dept] char(3) DEFAULT ('') NOT NULL,
    [BuildStep] tinyint DEFAULT (0) NOT NULL,
    [FrameSize] decimal(4,2) DEFAULT (0) NOT NULL,
    [HandlebarWidth] decimal(4,2) DEFAULT (0) NOT NULL,
    [OverallReach] decimal(4,2) DEFAULT (0) NOT NULL,
    [SaddleHeight] decimal(4,2) DEFAULT (0) NOT NULL,
    [TopTubeLength] decimal(4,2) DEFAULT (0) NOT NULL,
    [StemLength] decimal(4,2) DEFAULT (0) NOT NULL,
    [SaddleToStemDrop] decimal(3,2) DEFAULT (0) NOT NULL,
    [CrankLength] decimal(5,2) DEFAULT (0) NOT NULL,
    [FrontXPattern] varchar(50) DEFAULT ('') NOT NULL,
    [RearXPattern] varchar(50) DEFAULT ('') NOT NULL,
    [StyleCode] char(2) DEFAULT ('') NOT NULL,
    [PostToCustomer] bit DEFAULT (0) NOT NULL,
    [IsProblemForBuyer] bit DEFAULT (0) NOT NULL
)
GO

CREATE CLUSTERED INDEX [CL_IX_Build_Customer]
ON [Build] ([CustomerID] ASC)
GO

ALTER TABLE [Build] ADD CONSTRAINT [PK_Build] 
    PRIMARY KEY NONCLUSTERED ([BuildID])
GO

CREATE TABLE [BuildItem] ( 
    [BuildID] int NOT NULL,
    [DistributorNumber] varchar(30) DEFAULT ('') NOT NULL,
    [ProductVersionID] int,
    [ProductTypeID] int NOT NULL,
    [GroupType] smallint NOT NULL,
    [SortOrder] smallint NOT NULL,
    [DistributorID] int NOT NULL,
    [WebDescription] varchar(300) DEFAULT ('') NOT NULL,
    [WSDescription] varchar(300) DEFAULT ('') NOT NULL,
    [Quantity] tinyint DEFAULT (0) NOT NULL,
    [UnitGrams] decimal(7,2) DEFAULT (0) NOT NULL,
    [UnitCostPrice] smallmoney DEFAULT (0) NOT NULL,
    [UnitSalePrice] smallmoney DEFAULT (0) NOT NULL,
    [TotalCostPrice] smallmoney DEFAULT (0) NOT NULL,
    [TotalSalePrice] smallmoney DEFAULT (0) NOT NULL,
    [WSNotes] varchar(300) DEFAULT ('') NOT NULL,
    [ProductLocation] varchar(10) DEFAULT ('') NOT NULL
) 
GO

ALTER TABLE [BuildItem] ADD CONSTRAINT [PK_BuildItem] 
PRIMARY KEY CLUSTERED ([BuildID], [DistributorNumber]) 
GO

ALTER TABLE [BuildItem] ADD CONSTRAINT [FK_BuildItem_Build] 
FOREIGN KEY ([BuildID]) REFERENCES [Build] ([BuildID])
GO

CREATE TABLE [PurchaseOrder] ( 
    [PurchaseOrderID] int identity(1,1)  NOT NULL,
    [IsOpen] bit DEFAULT (1) NOT NULL,
    [DistributorID] int NOT NULL,
    [BuyerID] int,
    [CompanyContact] varchar(30) DEFAULT ('') NOT NULL,
    [ReferenceNumber] varchar(50) DEFAULT ('') NOT NULL,
    [ShippingType] varchar(20) DEFAULT ('') NOT NULL,
    [ShippingCost] smallmoney DEFAULT (0) NOT NULL,
    [OrderDate] datetime,
    [ETSDate] datetime,
    [ShippedDate] datetime,
    [ETADate] datetime,
    [RecievedDate] datetime,
    [TotalOrdered] decimal(5,2) DEFAULT (0) NOT NULL,
    [TotalRecieved] decimal(5,2) DEFAULT (0) NOT NULL,
    [WSNotes] varchar(300) DEFAULT ('') NOT NULL,
    [LastUpdated] datetime DEFAULT (getdate()) NOT NULL,
    [CreateDate] datetime DEFAULT (getdate()) NOT NULL
) 
GO

ALTER TABLE [PurchaseOrder] ADD CONSTRAINT [PK_PurchaseOrder] 
PRIMARY KEY CLUSTERED ([PurchaseOrderID]) 
GO

CREATE TABLE [PurchaseOrderItem2] ( 
    [PurchaseOrderItemID] int identity(1,1)  NOT NULL,
    [PurchaseOrderID] int NOT NULL,
    [BuildID] int NOT NULL,
    [ItemID] int NOT NULL,
    [ProductTypeName] varchar (30) NOT NULL,
    [DistributorNumber] varchar(30) NOT NULL,
    [Description] varchar(100) DEFAULT ('') NOT NULL,
    [QuantityOrdered] tinyint DEFAULT (0) NOT NULL,
    [UnitCost] smallmoney DEFAULT (0) NOT NULL
)
GO

ALTER TABLE [PurchaseOrderItem2]
    ADD CONSTRAINT [UQ_PurchaseOrderItem2_ItemID] UNIQUE ([ItemID])
GO

ALTER TABLE [PurchaseOrderItem2] ADD CONSTRAINT [PK_PurchaseOrderItem2] 
    PRIMARY KEY CLUSTERED ([PurchaseOrderItemID])
GO

-- This foreign key constraint wont allow me to insert rows into PurchaseOrderItem.
ALTER TABLE [PurchaseOrderItem2] ADD CONSTRAINT [FK_PurchaseOrderItem2_BuildItem]
    FOREIGN KEY ([ItemID]) REFERENCES [BuildItem] ([ItemID])
GO

ALTER TABLE [PurchaseOrderItem2] ADD CONSTRAINT [FK_PurchaseOrderItem2_PurchaseOrder] 
    FOREIGN KEY ([PurchaseOrderID]) REFERENCES [PurchaseOrder] ([PurchaseOrderID])
GO

The problem I now have is that the fk constraint from BuildItem.ItemID to PurchaseOrderItem.ItemID is making it impossible to add purchase order items to a purchase order which don't exist as a build item

If you want to add PurchaseOrderItems without having a corresponding BuildItem, then you shouldn't enforce the 1:1 relation with a unique constraint on the FK side. Instead you can have a trigger to check for existing non nullable values.

Or you might consider moving the FK to the other side. (BuildItem) And then you have to make sure to create a PurchaseOrderItem first, before creating a BuildItem (in one transaction).

OK, I went with your second suggestion of moving the FK to the BuildItem so I now have BuildItem.PurchaseOrderItemID and it's nullable so I can have build items which don't exist in a purchase order. Now I still have the same problem though. Because BuildItem.PurchaseOrderItemID has to be nullable I can't make it unique to create the one to one relation between BuildItem and PurchaseOrderItem. I didn't think this through before making the change. What now?

This is really a result of fiddling around with table DDL. Table DDL isn't your entity definition set, it's the RESULT of a projection of your entities onto a representation inside an RDBMS. This might sound fuzzy, but it's important. THe thing is that by having entity definitions (abstract entity definitions, like in NIAM/ORM) you define the entities in your system without DB related issues, and project them onto Table DDL or View DDL SQL. This projection doesn't have to mean that the entity definitions are 1:1 represented in the DB, but it DOES mean that if you have to make a change, your change is based on a fundamental change in your entity model, not in the projection result.

A typical metaphore is that if you want to change a line in your C# code, you do that in your C# code, not in the IL created from the C# code.

So moving the FK over to the other side is something you should only do if the relation flips in your ORM/NIAM model, which of course isn't the case in this situation.

A couple of things: - never ever make the mistake to modify your entity model based on what your 'screen' layout dictates. Screens come and go, your model is based on total different analysis and reasoning as your UI layouts. - FK constraints flow from FK side to PK side. So if you describe an FK constraint, it's defined on the FK side table, defining the FK side attributes as the FK side and defining the PK side attributes they're related to (which thus dictate the PK side table). Your initial FK flows from PurchaseOrderItem to BuildItem. - Why does anyone want to add a purchase order item if there's no builditem?

Looking at your initial model, you have create a separate entity for purchase order, and it relies on the existence of a build. BuildItem relies on the existence of Build and purchase order item relies on the existence of purchase order and builditem.

I don't think this is really that bad, except for one thing: why is it possible to add a purchase order item if the build item, which it depends on, isn't there? IMHO that's not possible. If it IS possible, you have found the root of your problem: because then the dependency between purchase order and build isn't really there and with that automatically the dependency between purchase order item and builditem

Otis wrote:

A couple of things: - Why does anyone want to add a purchase order item if there's no builditem?

Because BuildItems are what customer purchase from us. We now need to track the purchase of items which don't belong to a customer (order).

Otis wrote:

Looking at your initial model, you have create a separate entity for purchase order, and it relies on the existence of a build. BuildItem relies on the existence of Build and purchase order item relies on the existence of purchase order and builditem.

I don't think this is really that bad, except for one thing: why is it possible to add a purchase order item if the build item, which it depends on, isn't there? IMHO that's not possible. If it IS possible, you have found the root of your problem: because then the dependency between purchase order and build isn't really there and with that automatically the dependency between purchase order item and builditem

Yes, I agree. At the moment it's not possible to add a item to a purchase order. This needs to be changed so that we can order products don't belong to a customer. Yes, in a way I am breaking the dependency between PurchaseOrderItem and BuildItem. I want to make it something which is more optional. The only way to do this then is to allow the possibility of more then one BuildItem in a purchase order because I'm breaking the original dependency. In doing this there is of course other ways to make some of my features continue to work but it now just takes more code because the one to one relation is not there anymore.