Advice on Table and Joins - 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 2008 Forums

Transact-SQL (2008)

Advice on Table and Joins

Author

Topic

ianc22
Starting Member

2 Posts

Posted - 2011-02-14 : 08:48:49

Hi People,

I'm hitting my head against the wall here. The scenario thus far:

I have to save info for the following scenario. I have a contest that consists of different categories. Each category can have many deadlines and every deadline can have many fees.

Some Categories might have the same details for deadlines (i.e. linked tables?) with the either same fees for the individual deadlines or they may be different fees altogether.

I have created the tables as follow:

[Categories]
CategoryID (PK)
OtherField
OtherField

[Deadlines]
DeadlineID (PK)
OtherField
OtherField

[Categories_Deadlines]
Categories_DeadlinesID (PK)
CategoryID (FK)
DeadlineID (FK)

[Fees]
FeeID (PK)
[Deadlines_Fees]
Deadlines_FeesID (PK)
FeeID (FK)
Categories_DeadlinesID (FK)

Don't know if this is even the correct design, I'm still learning ;)

Once this is in place, acn someone please guide me in the right direction as far as teh Selects should be? with my knowledge I would try joins but I'm not sure if it will produce the correct results when it comes to the stage where there might be loads of contests on file.

Maybe using grouping, which is a whole new kettle of fish...

Any help/advice is greatly appreciated.

Thanks in advance

Ian

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2011-02-14 : 09:45:54

I am kind of guessing here because I don't know everything about what you are trying to do. But What I would suggest is that you use composit keys on some of the tables. (http://weblogs.sqlteam.com/jeffs/archive/2007/08/23/composite_primary_keys.aspx) I am posting the DDL for my suggestion and a diagram for you to view as well.


USE [TestDb]
GO

CREATE TABLE [dbo].[Category](
	[CategoryID] [int] IDENTITY(1,1) NOT NULL,
	[Description] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
(
	[CategoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Deadline](
	[DeadlineID] [int] IDENTITY(1,1) NOT NULL,
	[DeadlineValue] [int] NOT NULL,
	[Description] [nchar](10) NOT NULL,
 CONSTRAINT [PK_Deadline] PRIMARY KEY CLUSTERED 
(
	[DeadlineID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Fee](
	[FeeID] [int] IDENTITY(1,1) NOT NULL,
	[Description] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Fee] PRIMARY KEY CLUSTERED 
(
	[FeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Category_Deadline](
	[CategoryID] [int] NOT NULL,
	[DeadlineID] [int] NOT NULL,
 CONSTRAINT [PK_Category_Deadline] PRIMARY KEY CLUSTERED 
(
	[CategoryID] ASC,
	[DeadlineID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Category_Deadline]  WITH CHECK ADD  CONSTRAINT [FK_Category_Category_Deadline] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Category] ([CategoryID])
GO

ALTER TABLE [dbo].[Category_Deadline] CHECK CONSTRAINT [FK_Category_Category_Deadline]
GO

ALTER TABLE [dbo].[Category_Deadline]  WITH CHECK ADD  CONSTRAINT [FK_Deadline_Category_Deadline] FOREIGN KEY([DeadlineID])
REFERENCES [dbo].[Deadline] ([DeadlineID])
GO

ALTER TABLE [dbo].[Category_Deadline] CHECK CONSTRAINT [FK_Deadline_Category_Deadline]
GO
CREATE TABLE [dbo].[Fee_CategoryDeadline](
	[FeeID] [int] NOT NULL,
	[CategoryID] [int] NOT NULL,
	[DeadlineID] [int] NOT NULL,
 CONSTRAINT [PK_Fee_CategoryDeadline] PRIMARY KEY CLUSTERED 
(
	[FeeID] ASC,
	[CategoryID] ASC,
	[DeadlineID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Fee_CategoryDeadline]  WITH CHECK ADD  CONSTRAINT [FK_CategoryDeadline_to_Fee_CategoryDeadline] FOREIGN KEY([CategoryID], [DeadlineID])
REFERENCES [dbo].[Category_Deadline] ([CategoryID], [DeadlineID])
GO

ALTER TABLE [dbo].[Fee_CategoryDeadline] CHECK CONSTRAINT [FK_CategoryDeadline_to_Fee_CategoryDeadline]
GO

ALTER TABLE [dbo].[Fee_CategoryDeadline]  WITH CHECK ADD  CONSTRAINT [FK_Fee_to_Fee_CategoryDeadline] FOREIGN KEY([FeeID])
REFERENCES [dbo].[Fee] ([FeeID])
GO

===
http://www.ElementalSQL.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources