When is a SQL Server foreign key table too much?

Question

When I have the below two tables, would the StatusTypes table be considered as overkill? i.e. is there more benefit to using it than not?

In this situation I don't expect to have to load these statuses up in an admin backend in order to add or change/ delete them, but on the other hand I don't often like not using foreign keys.

I'm looking for reasons for and against separating out the status type or keeping it in the Audit table.

Any help would be appreciated.

 -- i.e. NEW, SUBMITTED, UPDATED CREATE TABLE [dbo].[StatusTypes]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](250) NOT NULL, CONSTRAINT [PK_StatusTypes] PRIMARY KEY CLUSTERED ([ID] ASC) ) ON [PRIMARY] GO CREATE TABLE [dbo].[Audits]( [ID] [int] IDENTITY(1,1) NOT NULL, [Description] [nvarchar](500) NULL, [Country_Fkey] [int] NOT NULL, [User_Fkey] [int] NOT NULL, [CreatedDate] [date] NOT NULL, [LastAmendedDate] [date] NULL, [Status_Fkey] [int] NOT NULL, CONSTRAINT [PK_Audits] PRIMARY KEY CLUSTERED ([ID] ASC) ) ON [PRIMARY] GO

Enforcing referential integrity in a database is absolutely crucial for it to work properly and provide value to its users. Therefore, I would never say a lookup table to enforce a foreign key (and its possible values) is too much - you cannot have too many safety precautions in a database - only too few.... — marc_s
– marc_s, Commented Dec 8, 2012 at 21:56
This was pretty much what I was thinking but I wanted to get some other opinions on the situation because it could go either way. Thanks — Pricey
– Pricey, Commented Dec 8, 2012 at 22:16

djb · Accepted Answer · 2012-12-08 22:39:35Z

In this situation I like to keep the lookup table to enforce the status being one of a set of types. Some databases have an enum type, or can use check constraints, but this is the most portable method IMO.

However, I make the lookup table containing only a single string column containing the type's name. That way you don't have to actually join to the lookup table and your ORM (assuming you use one) can be completely unaware of it.

In this case the schema would look like:

CREATE TABLE [dbo].[StatusTypes]( [ID] [nvarchar](250) NOT NULL, CONSTRAINT [PK_StatusTypes] PRIMARY KEY CLUSTERED ([ID] ASC) ) ON [PRIMARY] GO CREATE TABLE [dbo].[Audits]( [ID] [int] IDENTITY(1,1) NOT NULL, ... [Status] [nvarchar](250) NOT NULL, CONSTRAINT [PK_Audits] PRIMARY KEY CLUSTERED ([ID] ASC), CONSTRAINT [FK_Audit_Status] FOREIGN KEY (Status) REFERENCES StatusTypes(ID) ) ON [PRIMARY] GO

And a query for audit items of a particular type would be:

SELECT ... FROM Audits WHERE Status = 'ACTIVE'

So referential integrity is still enforced but queries don't need an extra join.

so are you saying you would keep the StatusTypes table and also store the text not the foreign key for the StatusType in the Audits table?
Added an example to my answer. My view is to not have an unnecessary numeric ID when you are trying to restrict a column to one of a set of valid strings.
Ah ok, I get it, using a foreign key constraint interesting concept and like you say I'll be using an enum in my system and this means one less join. Not bad either.
Be wary of using the string value directly in the table. It can work in some instances but fail badly in others. You have to worry about possible dirty data and the need to potentially change values, not to mention storage requirements if you will have many rows.
@John Yea in this case it should be ok, but I have another case where I am storing a lot of rows and so storage is a factor. As for bad data, the foreign key constraint will help with that as I can then guarantee that the status types in the Audit table will match what is in the status table, so it should be fairly simple to change specific ones if required.

usr · Accepted Answer · 2012-12-08 22:32:28Z

I'll offer a counter-argument: Use your development time where it is most useful. Maybe you don't need this runtime-check that much. Maybe you can use your development time for some other check that is more useful.

Is it even likely that an invalid status value will be set? You application surely uses a set of constants or an enum so it is unlikely that some rogue value slips in.

That said, there is a lot of value in ensuring integrity. I like to cover all my "enum" columns with a BETWEEN check constraint which is quickly done and even faster at runtime.

Collectives™ on Stack Overflow

When is a SQL Server foreign key table too much?

2 Answers 2

5 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Related