Extension Table vs Nullable Foreign Keys

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Extension Table vs Nullable Foreign Keys

Author

Topic

TalMcMahon
Starting Member

4 Posts

Posted - 2015-02-10 : 12:26:52

Hi,
This is a typical in office debate I would love some feedback on.

What are the pros and cons of these 2 solutions for records that may be arbitrarily grouped by a user into zero or one group.

Solution 1.

Base table with a nullable foreign key to the group table:


CREATE TABLE [dbo].[Foo](
	[FooId] [int] NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[FooGroupId] [int] NULL,
 CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED 
(
	[FooId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
  
CREATE TABLE [dbo].[FooGroup](
	[FooGroupId] [int] NOT NULL,
	[Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_FooGroup] PRIMARY KEY CLUSTERED 
(
	[FooGroupId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

 
ALTER TABLE [dbo].[Foo]  WITH CHECK ADD  CONSTRAINT [FK_Foo_FooGroup] FOREIGN KEY([FooGroupId])
REFERENCES [dbo].[FooGroup] ([FooGroupId])

Solution 2.
Base Table, Extension of Base table with foreign Key to Group Table:


CREATE TABLE [dbo].[Bar](
	[BarId] [int] NOT NULL,
	[Name] [varchar](50) NULL,
 CONSTRAINT [PK_Bar] PRIMARY KEY CLUSTERED 
(
	[BarId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[BarGroup](
	[BarGroupId] [int] NOT NULL,
	[Name] [varchar](50) NULL,
 CONSTRAINT [PK_BarGroup] PRIMARY KEY CLUSTERED 
(
	[BarGroupId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[BarGroupBarExtension](
	[BarId] [int] NOT NULL,
	[BarGroupId] [int] NOT NULL,
 CONSTRAINT [PK_BarGroupBarExtension] PRIMARY KEY CLUSTERED 
(
	[BarId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


ALTER TABLE [dbo].[BarGroupBarExtension]  WITH CHECK ADD  CONSTRAINT [FK_BarGroupBarExtension_Bar] FOREIGN KEY([BarId])
REFERENCES [dbo].[Bar] ([BarId])
GO
 
ALTER TABLE [dbo].[BarGroupBarExtension]  WITH CHECK ADD  CONSTRAINT [FK_BarGroupBarExtension_BarGroup] FOREIGN KEY([BarGroupId])
REFERENCES [dbo].[BarGroup] ([BarGroupId])
GO

The 2nd way seems the most "correct" from a Database point of view, but the first way makes it much easier to code against. Like I said we are looking for outside opinions and discussion.

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2015-02-10 : 20:42:19

As you stated, the 2nd option is more relationally sound. It also allows you to create a view that presents the same structure as the Foo table in the first solution:

CREATE VIEW Foo(FooID, Name, FooGroupID) AS
SELECT Bar.BarID, Bar.Name, BarGroupBarExtension.BarGroupID
FROM Bar
LEFT JOIN BarGroupBarExtension ON Bar.BarID=BarGroupBarExtension.BarID

Probably not that useful though, unless you had legacy code that requires those columns. However it would be much harder, probably impossible, to reconstruct the 2nd solution from the first.

The 2nd solution, by avoiding nullable columns, also avoids a type of null-value ambiguity (I can't remember the proper term for it, but if you read Chris Date he'll explain it). If a joined query projects NULL in a column, you know it's because of an unmatched/nonexistent row in BarGroupBarExtension.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources