March 24, 2008 at 1:53 pm
Hi,
Let's say you have these 3 tables
--------------------------------------------------------------
use tempdb
go
create table TableMain (
RowID int primary key identity(1, 1)
, ItemTypeID tinyint NOT NULL
, Details varchar(200) NOT NULL
)
go
create table TableDetails (
DetailRowID int primary key identity(1, 1)
, RowID int NOT NULL
, ItemTypeID tinyint NOT NULL
, Details varchar(200) NOT NULL
)
go
create table ItemTypes (
ItemTypeID tinyint primary key identity(1, 1)
, Details varchar(50) NOT NULL
)
go
create unique nonclustered index IX_TableMain__ItemTypeID__RowID on TableMain (ItemTypeID, RowID)
go
alter table TableDetails
add constraint FK_TableDetails_TableMain__RowID__ItemTypeID foreign key (ItemTypeID, RowID) references TableMain(ItemTypeID, RowID);
go
alter table TableMain
add constraint FK_TableMain_ItemTypes__ItemTypeID foreign key (ItemTypeID) references ItemTypes(ItemTypeID);
go
/*
drop table TableDetails
drop table TableMain
drop table ItemTypes
*/
--------------------------------------------------------------
As you can see TableDetails references TableMain by ItemTypeID and RowID. TableMain also has a foreign key on ItemTypeID.
In this example although there is no references between TableDetails and ItemTypes on ItemTypeID field, referential integrity is still maintain because of the foreign key on TableMain (ItemTypeID, RowID)
So here is my question. Although referential integrity is maintain with this structure, would you guys still create a foreign key on TableDetails (ItemTypeID), i.e.:
alter table TableDetails
add constraint FK_TableDetails_ItemTypes__ItemTypeID foreign key (ItemTypeID) references ItemTypes(ItemTypeID);
go
There might not be any right or wrong, it might be a personal preference sort of thing. To me it seems performance wise it's better to not create this extra contraints since it doesn't add any additional integrity, on the other hand when looking at a DB schema this extra constraint might help understanding what's going on. Also perhaps it helps SQL Server in picking the right execution plan but that I am not sure.
Perhaps the solution is to create the constraint with a NOCHECK on it... is it?
March 24, 2008 at 2:33 pm
Which does the ItemTypeID relate to? TableMain or TableDetail? You shouldn't have it in all three tables. It's redundant. If it applies to the row, then put the type id in TableMain, if it applies to the detail, then put it there....
Also, you seem to have details spread out into all three tables. This is against normalization. Just think if you ever had to change the value of the "Details" column, you'd have to do it three places.
It seems to me, you need to do something like the following:
USE tempdb
GO
CREATE TABLE TableMain
(RowID INT PRIMARY KEY IDENTITY(1, 1)
,ItemTypeID TINYINT NOT NULL)
GO
CREATE TABLE TableDetails
(DetailRowID INT PRIMARY KEY IDENTITY(1, 1)
,RowID INT NOT NULL)
GO
CREATE TABLE ItemTypes
(ItemTypeID TINYINT PRIMARY KEY IDENTITY(1, 1)
,Details VARCHAR(50) NOT NULL)
GO
CREATE UNIQUE NONCLUSTERED INDEX IX_TableMain__ItemTypeID_RowID ON TableMain (ItemTypeID, RowID)
GO
ALTER TABLE TableDetails
ADD CONSTRAINT FK_TableDetails_TableMain__RowID FOREIGN KEY (RowID) REFERENCES TableMain (RowID);
GO
ALTER TABLE TableMain
ADD CONSTRAINT FK_TableMain_ItemTypes__ItemTypeID FOREIGN KEY (ItemTypeID) REFERENCES ItemTypes (ItemTypeID);
GO
/*
drop table TableDetails
drop table TableMain
drop table ItemTypes
*/
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 24, 2008 at 3:12 pm
The Details column name is just for the sample. This little schema up there was just put together for showing an example. The details column can be ignored in my example.
The reason why ItemTypeID was duplicated in this example is that in the schema I am working on there is a different 'TableDetails' kind of table for each ItemTypeID. It's a one to one relationship between TableMain and each one of the TableDetails. An example of why you would want to do such thing would be... Let's say you've got a Transactions table that has a TranTypeID column. When TranTypeID is a 'creditcard chargeback' then the transaction details would end up in let's say TransactionChargebacks. But if the TranTypeID is 'CreditCard' then the transaction details would end up in let's say TransactionCreditCards...
The design decision is discussed here: http://www.sqlservercentral.com/Forums/Topic468308-361-1.aspx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply