Which approach is better for this scenario?

Question

Hello SQL Gurus. We have the following table:

CREATE TABLE [dbo].[CampaignCustomer](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CampaignID] [int] NOT NULL,
    [CustomerID] [int] NULL,
    [CouponCode] [nvarchar](20) NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [ModifiedDate] [datetime] NULL,
    [Active] [bit] NOT NULL,
 CONSTRAINT [PK_CampaignCustomer] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

and the following Unique Index:

CREATE UNIQUE NONCLUSTERED INDEX [IX_CampaignCustomer_CouponCode] ON [dbo].[CampaignCustomer] 
(
    [CouponCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 20) ON [PRIMARY]
GO

We do pretty constant queries using the CouponCode and other foreign keys (not shown above for simplicity). The CampaignCustomer table has almost 4 million records and growing. We also do campaigns that don't require Coupon Codes and therefore we don't insert those records. Now we need to also start tracking those campaigns as well for another purpose. So we have 2 options:

1. We change the CouponCode column ot allow nulls and create a unique filetered index to not include nulls and allow the table to grow even bigger and faster.
2. Create a separate table for tracking all campaigns for this specific purpose.

Keep in mind that the CampaignCustomer table is used very often for redeeming coupons and inserting new ones. Bottom line is we don't want our customer to redeem a coupon and stay waiting until they give up or for other processes to fail. So, from an efficiency perspective, which option do you think is best and why?

Answer 1

I'd go for the filtered index... you're storing the same data so keep it in the same table.

Splitting the table is refactoring when you probably don't need it and adds complexity.

Do you have problems with 4 million rows? It's not that much especially for such a narrow table

Answer 2

1. I'm against a duplicate table for the sake of a single column
2. Allowing the couponcode to be null means that someone could accidentally create a record where the value is NULL when it should be a valid couponcode

I would create a couponcode that indicates as being a non-coupon rather than resorting to indicator columns "isCoupon" or "isNonCouponCampaign", and use a filtered index to ignore the "nocoupon" value.

Which leads to my next point - I don't see a foreign key reference, but it would be key to knowing what coupons existed and which ones were actually used. Some of the columns in the existing table could be moved up to the parent couponcode table...