5 column Composite Key - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

5 column Composite Key

Author

Topic

jhuckabee
Starting Member

5 Posts

Posted - 2004-12-20 : 14:19:32

I am designing a database based on 5 core objects and have run into a scenario where I have a table whose primary key is a composite key of all of the 5 core objects. This happened as a result of relationships between multiple associative entities (whose primary keys are also composites of the core tables). I'm just curious if anyone else has run into the same situation - meaning this is ok - or if this is just poor design on my part. (I am a newbie at db design)

Are there any performance degredation issues by having this many columns form a primary key?

Thanks in advance for your help.

-Josh

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-20 : 14:33:28

well it's ok if you don't mind including all of them in a join... that's a lot of typing

personally i usually never go over 2 colun composite key.

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-20 : 14:46:00

a 5-column PK is fine, if that's what your data needs.

Sometimes it might be best to make that primary key index non-clustered, and add a clustered identity. depends on the columns in your PK and how data is inserted -- if the 5-column PK is clustered, SQL may need to do a lot of restructuring of the storage of the data every time rows are added or deleted.

- Jeff

jhuckabee
Starting Member

5 Posts

Posted - 2004-12-20 : 14:53:07

Thanks for the quick reply.

How would I go about adding a "clustered identity". I assume this means add another column to the table which is an identity field and then create a clustered index on that column???

Thanks again - Josh

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-20 : 14:55:19

basically, that's it. it's only a thought. you wouldn't make the identity your primary key, but you'd just put a clsutered index on it.

you'd have to test to see it's worth it, i suspect probably not at least initially. I'd leave it out. but if you notice that DELETE's and INSERTs are taking a very long time, you might try this option.

If things are fine, or the table isn't huge, I'd leave it alone.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2004-12-20 : 16:31:52

quote:
Originally posted by jsmith8858

Sometimes it might be best to make that primary key index non-clustered, and add a clustered identity.

AAAAAAAAAAAAAAHHHHHHHHHHHHHHHHHHHHHHH

Post the DDL of the Table.

Brett

8-)

jhuckabee
Starting Member

5 Posts

Posted - 2004-12-20 : 18:14:25

CREATE TABLE [dbo].[tblFundingCode] (
[FundingCode] [int] NOT NULL ,
[SegmentID] [int] NULL ,
[DiscCode] [int] NULL ,
[CreditProgCode] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblFundingSegmentProgram] (
[FundingCode] [int] NOT NULL ,
[SegmentID] [int] NOT NULL ,
[ProgramID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblFundingSegmentProgramRange] (
[SegmentID] [int] NOT NULL ,
[ProgramID] [int] NOT NULL ,
[RangeID] [int] NOT NULL ,
[FundingCode] [int] NOT NULL ,
[LineNum] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblPlan] (
[PlanNumber] [int] NOT NULL ,
[PaymentFactor] [float] NULL ,
[ImpliedTerm] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblProgram] (
[ProgramID] [int] IDENTITY (1, 1) NOT NULL ,
[ProgramTypeID] [int] NULL ,
[Abbreviation] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Program] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Order] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblProgramType] (
[ProgramTypeID] [int] IDENTITY (1, 1) NOT NULL ,
[ProgramType] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProgramDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Footnote] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblRange] (
[RangeID] [int] IDENTITY (1, 1) NOT NULL ,
[Low] [money] NULL ,
[High] [money] NULL ,
[Wording] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblSegment] (
[SegmentID] [int] IDENTITY (1, 1) NOT NULL ,
[Segment] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SegNumber] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblSegmentProgram] (
[SegmentID] [int] NOT NULL ,
[ProgramID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblSegmentProgramTier] (
[SegmentID] [int] NOT NULL ,
[ProgramID] [int] NOT NULL ,
[TierID] [int] NOT NULL ,
[APR] [float] NULL ,
[FundingPercent] [float] NULL ,
[PeriodicRate] [float] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblSegmentProgramTierFundingRange] (
[SegmentID] [int] NOT NULL ,
[ProgramID] [int] NOT NULL ,
[TierID] [int] NOT NULL ,
[FundingCode] [int] NOT NULL ,
[RangeID] [int] NOT NULL ,
[PlanNumber] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTier] (
[TierID] [int] IDENTITY (1, 1) NOT NULL ,
[Tier] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LowScore] [int] NULL ,
[HighScore] [int] NULL ,
[Special] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblFundingCode] WITH NOCHECK ADD
CONSTRAINT [PK_tblFundingCode] PRIMARY KEY CLUSTERED
(
[FundingCode]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblFundingSegmentProgram] WITH NOCHECK ADD
CONSTRAINT [PK_tblFundingSegmentProgram] PRIMARY KEY CLUSTERED
(
[FundingCode],
[SegmentID],
[ProgramID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblFundingSegmentProgramRange] WITH NOCHECK ADD
CONSTRAINT [PK_tblFundingSegmentProgramRange] PRIMARY KEY CLUSTERED
(
[SegmentID],
[ProgramID],
[RangeID],
[FundingCode]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblPlan] WITH NOCHECK ADD
CONSTRAINT [PK_tblPlan2] PRIMARY KEY CLUSTERED
(
[PlanNumber]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblProgram] WITH NOCHECK ADD
CONSTRAINT [PK_tblProgram] PRIMARY KEY CLUSTERED
(
[ProgramID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblProgramType] WITH NOCHECK ADD
CONSTRAINT [PK_tblProgramType] PRIMARY KEY CLUSTERED
(
[ProgramTypeID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblRange] WITH NOCHECK ADD
CONSTRAINT [PK_tblRange] PRIMARY KEY CLUSTERED
(
[RangeID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblSegment] WITH NOCHECK ADD
CONSTRAINT [PK_tblSegment] PRIMARY KEY CLUSTERED
(
[SegmentID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblSegmentProgram] WITH NOCHECK ADD
CONSTRAINT [PK_tblSegmentProgram] PRIMARY KEY CLUSTERED
(
[SegmentID],
[ProgramID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblSegmentProgramTier] WITH NOCHECK ADD
CONSTRAINT [PK_tblProgramTier] PRIMARY KEY CLUSTERED
(
[SegmentID],
[ProgramID],
[TierID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblSegmentProgramTierFundingRange] WITH NOCHECK ADD
CONSTRAINT [PK_tblSegmentProgramTierPlan] PRIMARY KEY CLUSTERED
(
[SegmentID],
[ProgramID],
[TierID],
[FundingCode],
[RangeID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTier] WITH NOCHECK ADD
CONSTRAINT [PK_tblCreditTier] PRIMARY KEY CLUSTERED
(
[TierID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblFundingCode] ADD
CONSTRAINT [FK_tblFundingCode_tblSegment] FOREIGN KEY
(
[SegmentID]
) REFERENCES [dbo].[tblSegment] (
[SegmentID]
)
GO

ALTER TABLE [dbo].[tblFundingSegmentProgram] ADD
CONSTRAINT [FK_tblFundingSegmentProgram_tblFundingCode] FOREIGN KEY
(
[FundingCode]
) REFERENCES [dbo].[tblFundingCode] (
[FundingCode]
),
CONSTRAINT [FK_tblFundingSegmentProgram_tblSegmentProgram] FOREIGN KEY
(
[SegmentID],
[ProgramID]
) REFERENCES [dbo].[tblSegmentProgram] (
[SegmentID],
[ProgramID]
)
GO

ALTER TABLE [dbo].[tblFundingSegmentProgramRange] ADD
CONSTRAINT [FK_tblFundingSegmentProgramRange_tblFundingSegmentProgram] FOREIGN KEY
(
[FundingCode],
[SegmentID],
[ProgramID]
) REFERENCES [dbo].[tblFundingSegmentProgram] (
[FundingCode],
[SegmentID],
[ProgramID]
),
CONSTRAINT [FK_tblFundingSegmentProgramRange_tblRange] FOREIGN KEY
(
[RangeID]
) REFERENCES [dbo].[tblRange] (
[RangeID]
)
GO

ALTER TABLE [dbo].[tblProgram] ADD
CONSTRAINT [FK_tblProgram_tblProgramType] FOREIGN KEY
(
[ProgramTypeID]
) REFERENCES [dbo].[tblProgramType] (
[ProgramTypeID]
)
GO

ALTER TABLE [dbo].[tblSegmentProgram] ADD
CONSTRAINT [FK_tblSegmentProgram_tblProgram] FOREIGN KEY
(
[ProgramID]
) REFERENCES [dbo].[tblProgram] (
[ProgramID]
),
CONSTRAINT [FK_tblSegmentProgram_tblSegment] FOREIGN KEY
(
[SegmentID]
) REFERENCES [dbo].[tblSegment] (
[SegmentID]
)
GO

ALTER TABLE [dbo].[tblSegmentProgramTier] ADD
CONSTRAINT [FK_tblSegmentProgramTier_tblSegmentProgram] FOREIGN KEY
(
[SegmentID],
[ProgramID]
) REFERENCES [dbo].[tblSegmentProgram] (
[SegmentID],
[ProgramID]
),
CONSTRAINT [FK_tblSegmentProgramTier_tblTier] FOREIGN KEY
(
[TierID]
) REFERENCES [dbo].[tblTier] (
[TierID]
)
GO

ALTER TABLE [dbo].[tblSegmentProgramTierFundingRange] ADD
CONSTRAINT [FK_tblSegmentProgramTierFundingRange_tblFundingSegmentProgramRange] FOREIGN KEY
(
[SegmentID],
[ProgramID],
[RangeID],
[FundingCode]
) REFERENCES [dbo].[tblFundingSegmentProgramRange] (
[SegmentID],
[ProgramID],
[RangeID],
[FundingCode]
),
CONSTRAINT [FK_tblSegmentProgramTierFundingRange_tblPlan] FOREIGN KEY
(
[PlanNumber]
) REFERENCES [dbo].[tblPlan] (
[PlanNumber]
),
CONSTRAINT [FK_tblSegmentProgramTierFundingRange_tblSegmentProgramTier] FOREIGN KEY
(
[SegmentID],
[ProgramID],
[TierID]
) REFERENCES [dbo].[tblSegmentProgramTier] (
[SegmentID],
[ProgramID],
[TierID]
)
GO

jhuckabee
Starting Member

5 Posts

Posted - 2004-12-20 : 18:17:30

The entire database as it currently exists is posted above. The table I'm talking about is tblSegmentProgramTierFundingRange.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-20 : 18:19:34

All ints in the PK for this table. I don't see a problem with this. I don't see the point of adding an identity column instead of using this composite PK. Of course, the only way to know for sure is to test out the performance.

Tara

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-12-20 : 19:25:42

Hi Josh,

Question.. Are you aware of the implications of natural vs surrogate key selections? I am not trying to start another religous war about key selection :) just trying to determine if you are aware of the fundemental issues surrounding natural vs surrogate keys.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-12-20 : 21:01:21

You might also want to ask yourself does the table have a true natural key. :) This will really get a discussion started here. Have you identified the attributes and underlying processes correctly in the data model? Will you need to store historic ranges? Will the ranges change over time. Will that be reflected in matrix edits or addition/deletion? Does the table need a primary key at all (this should be fun)?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-21 : 14:14:34

Trolling, Derrick?

HTH

=================================================================

The best things in life are nearest: Breath in your nostrils, light in your eyes, flowers at your feet, duties at your hand, the path of right just before you. -Robert Louis Stevenson, novelist, essayist, and poet
(1850-1894)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-12-21 : 15:20:24

lol Not at all. You should actually ask the above questions and seriously consider what they mean. I don't think going through a serious thought process is trolling.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jhuckabee
Starting Member

5 Posts

Posted - 2004-12-21 : 15:21:53

I'm pretty sure I have correctly identified all of the attributes and processes in my data model. And yes, the objects do have a natural key that I can use to replace the surrogates I'm currently using. That should make reading these records more meaningful now.

Another thought I had was to eliminate some of these associative entities and replacing them with a single table that has foreign keys back to the core objects.... I'm still tossing this stuff around in my head, but thankfully a few hours sleep and your suggestions have broken my tunnel view on this model that began developing late yesterday afternoon.

What do you think about the elimination of the associative entities?

And Derrick...I appreciate your thought provoking questions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources