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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Extension Table vs Nullable Foreign Keys
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

TalMcMahon
Starting Member

4 Posts

Posted - 02/10/2015 :  12:26:52  Show Profile  Reply with Quote
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

USA
15732 Posts

Posted - 02/10/2015 :  20:42:19  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000