normalization questions

My disclaimer:
I am by no means a design expert. My goal here is to learn, not criticize.
With that out of the way... Im confused. I was just reading this article:
http://www.sqlservercentral.com/columnists/bkelley/normalization.asp
Thank you BKelly, and again Im just trying to understand here. I have
several questions about the end result.
1; If I want to query to see all football players, I have to query Sports,
Team, Contract, and Clients. Mostly on character data types. Would it make
more sense to have a lookup table as in my DDL below. This would probably be
a very common query, and it seems that this would be a faster way.
2; What if an Agent wants to represent more than 1 Sport?
3; What if a Sport has more than 1 Agent?
4; Isnt LName, FName in the same column a violation of something?
5; In my design, Sport can be type-oed in the Team table. Would it be better
for me to have a Sport table with a SportID column, and relationships on that
column?
6; Again in my design, if I want to see all the Teams an Agent represents
(probably a common query), I need to go through several tables. Would I be
better off to have a lookup table between them?
7; Are most of these questions really just dependant on the requirements,
which obviously we dont know all of?
8; Am I just way out in left field with my design?
What I did here was designed the way I think it should be, and am hoping to
get some constructive criticism as well as answerrs to the above questions.
All points, good and bad, are welcomed. DDL below. Again, to learn here is my
only aim.
BE NICE JOE CELKO!
use [SportsAgency]
GO
CREATE TABLE [dbo].[Agent] (
[AgentId] [int] IDENTITY (1, 1) NOT NULL ,
[AgentLName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AgentFName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Client] (
[ClientID] [int] IDENTITY (1, 1) NOT NULL ,
[ClientLName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ClientFName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[LKPClientAgent] (
[ClientID] [int] NOT NULL ,
[AgentId] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[LKPClientTeam] (
[ClientID] [int] NOT NULL ,
[TeamID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Salary] (
[ClientID] [int] NOT NULL ,
[TeamID] [int] NOT NULL ,
[YearlySalary] [money] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Team] (
[TeamID] [int] IDENTITY (1, 1) NOT NULL ,
[TeamName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Sport] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Agent] WITH NOCHECK ADD
CONSTRAINT [PK_Agent] PRIMARY KEY CLUSTERED
(
[AgentId]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Client] WITH NOCHECK ADD
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[ClientID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LKPClientAgent] WITH NOCHECK ADD
CONSTRAINT [PK_LKPClientAgent] PRIMARY KEY CLUSTERED
(
[ClientID],
[AgentId]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LKPClientTeam] WITH NOCHECK ADD
CONSTRAINT [PK_LKPClientTeam] PRIMARY KEY CLUSTERED
(
[ClientID],
[TeamID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Salary] WITH NOCHECK ADD
CONSTRAINT [PK_Salary] PRIMARY KEY CLUSTERED
(
[ClientID],
[TeamID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Team] WITH NOCHECK ADD
CONSTRAINT [PK_Team] PRIMARY KEY CLUSTERED
(
[TeamID]
) ON [PRIMARY]
GO
CREATE INDEX [IX_Client] ON [dbo].[Client]([ClientID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LKPClientAgent] ADD
CONSTRAINT [FK_LKPClientAgent_Agent] FOREIGN KEY
(
[AgentId]
) REFERENCES [dbo].[Agent] (
[AgentId]
),
CONSTRAINT [FK_LKPClientAgent_Client] FOREIGN KEY
(
[ClientID]
) REFERENCES [dbo].[Client] (
[ClientID]
)
GO
ALTER TABLE [dbo].[LKPClientTeam] ADD
CONSTRAINT [FK_LKPClientTeam_Client] FOREIGN KEY
(
[ClientID]
) REFERENCES [dbo].[Client] (
[ClientID]
),
CONSTRAINT [FK_LKPClientTeam_Team] FOREIGN KEY
(
[TeamID]
) REFERENCES [dbo].[Team] (
[TeamID]
)
GO
ALTER TABLE [dbo].[Salary] ADD
CONSTRAINT [FK_Salary_Client] FOREIGN KEY
(
[ClientID]
) REFERENCES [dbo].[Client] (
[ClientID]
),
CONSTRAINT [FK_Salary_Team] FOREIGN KEY
(
[TeamID]
) REFERENCES [dbo].[Team] (
[TeamID]
)
GO
--
TIA,
ChrisRComments inline...
--
David Portas
SQL Server MVP
--
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:3BC29E40-C32D-44CE-8C0C-4778156B27DF@.microsoft.com...
> My disclaimer:
> I am by no means a design expert. My goal here is to learn, not criticize.
> With that out of the way... Im confused. I was just reading this article:
> http://www.sqlservercentral.com/columnists/bkelley/normalization.asp
> Thank you BKelly, and again Im just trying to understand here. I have
> several questions about the end result.
> 1; If I want to query to see all football players, I have to query Sports,
> Team, Contract, and Clients. Mostly on character data types. Would it make
> more sense to have a lookup table as in my DDL below. This would probably
> be
> a very common query, and it seems that this would be a faster way.
Perhaps. But you've missed out some vital constraints. For example your
design allows the same agent to be entered multiple times. IDENTITY is not
an integrity constraint so it shouldn't be the only key of any table. BTW I
try to avoid the term "lookup table" when discussing logical design. I think
that term misleads the inexperienced into thinking that those tables are
somehow inherently different to others. Logically, there is only one type of
table in an RDBMS.
> 2; What if an Agent wants to represent more than 1 Sport?
> 3; What if a Sport has more than 1 Agent?
Then you have a many-to-many relationship. To implement that use a "joining
table" that contains the keys from both Agent and Sport.
> 4; Isnt LName, FName in the same column a violation of something?
Not formally a violation of NF, no. But if you need to represent that
element of data somewhere as two separate values then it probably makes
sense to define them as separate attributes.
> 5; In my design, Sport can be type-oed in the Team table. Would it be
> better
> for me to have a Sport table with a SportID column, and relationships on
> that
> column?
It would be better to create a *constraint* to enforce the business rule
that only valid sports can be entered. That could be a check constraint or
more likely a foreign key constraint to a separate Sport table. Creating a
"sportid" as a surrogate key is irrelevant to enforcing the business rule -
it's purely a physical model issue.
> 6; Again in my design, if I want to see all the Teams an Agent represents
> (probably a common query), I need to go through several tables. Would I be
> better off to have a lookup table between them?
Don't see how it would help the query but it's probably a better design
because of 2 and 3 above.
> 7; Are most of these questions really just dependant on the requirements,
> which obviously we dont know all of?
YES
> 8; Am I just way out in left field with my design?
>
You are taking the wrong approach to learning this. Get a book and learn the
basics of the Relational Model first. Then you will be ready to start
applying your knowledge to SQL tables. Otherwise you are like a
mathematician trying to teach yourself algebra by playing with a pocket
calculator.
> What I did here was designed the way I think it should be, and am hoping
> to
> get some constructive criticism as well as answerrs to the above
> questions.
> All points, good and bad, are welcomed. DDL below. Again, to learn here is
> my
> only aim.
> BE NICE JOE CELKO!
>
>

normalization questions

My disclaimer:
I am by no means a design expert. My goal here is to learn, not criticize.
With that out of the way... Im confused. I was just reading this article:
http://www.sqlservercentral.com/colu...malization.asp
Thank you BKelly, and again Im just trying to understand here. I have
several questions about the end result.
1; If I want to query to see all football players, I have to query Sports,
Team, Contract, and Clients. Mostly on character data types. Would it make
more sense to have a lookup table as in my DDL below. This would probably be
a very common query, and it seems that this would be a faster way.
2; What if an Agent wants to represent more than 1 Sport?
3; What if a Sport has more than 1 Agent?
4; Isnt LName, FName in the same column a violation of something?
5; In my design, Sport can be type-oed in the Team table. Would it be better
for me to have a Sport table with a SportID column, and relationships on that
column?
6; Again in my design, if I want to see all the Teams an Agent represents
(probably a common query), I need to go through several tables. Would I be
better off to have a lookup table between them?
7; Are most of these questions really just dependant on the requirements,
which obviously we dont know all of?
8; Am I just way out in left field with my design?
What I did here was designed the way I think it should be, and am hoping to
get some constructive criticism as well as answerrs to the above questions.
All points, good and bad, are welcomed. DDL below. Again, to learn here is my
only aim.
BE NICE JOE CELKO!
use [SportsAgency]
GO
CREATE TABLE [dbo].[Agent] (
[AgentId] [int] IDENTITY (1, 1) NOT NULL ,
[AgentLName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AgentFName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Client] (
[ClientID] [int] IDENTITY (1, 1) NOT NULL ,
[ClientLName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ClientFName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[LKPClientAgent] (
[ClientID] [int] NOT NULL ,
[AgentId] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[LKPClientTeam] (
[ClientID] [int] NOT NULL ,
[TeamID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Salary] (
[ClientID] [int] NOT NULL ,
[TeamID] [int] NOT NULL ,
[YearlySalary] [money] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Team] (
[TeamID] [int] IDENTITY (1, 1) NOT NULL ,
[TeamName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Sport] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Agent] WITH NOCHECK ADD
CONSTRAINT [PK_Agent] PRIMARY KEY CLUSTERED
(
[AgentId]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Client] WITH NOCHECK ADD
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[ClientID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LKPClientAgent] WITH NOCHECK ADD
CONSTRAINT [PK_LKPClientAgent] PRIMARY KEY CLUSTERED
(
[ClientID],
[AgentId]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LKPClientTeam] WITH NOCHECK ADD
CONSTRAINT [PK_LKPClientTeam] PRIMARY KEY CLUSTERED
(
[ClientID],
[TeamID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Salary] WITH NOCHECK ADD
CONSTRAINT [PK_Salary] PRIMARY KEY CLUSTERED
(
[ClientID],
[TeamID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Team] WITH NOCHECK ADD
CONSTRAINT [PK_Team] PRIMARY KEY CLUSTERED
(
[TeamID]
) ON [PRIMARY]
GO
CREATE INDEX [IX_Client] ON [dbo].[Client]([ClientID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LKPClientAgent] ADD
CONSTRAINT [FK_LKPClientAgent_Agent] FOREIGN KEY
(
[AgentId]
) REFERENCES [dbo].[Agent] (
[AgentId]
),
CONSTRAINT [FK_LKPClientAgent_Client] FOREIGN KEY
(
[ClientID]
) REFERENCES [dbo].[Client] (
[ClientID]
)
GO
ALTER TABLE [dbo].[LKPClientTeam] ADD
CONSTRAINT [FK_LKPClientTeam_Client] FOREIGN KEY
(
[ClientID]
) REFERENCES [dbo].[Client] (
[ClientID]
),
CONSTRAINT [FK_LKPClientTeam_Team] FOREIGN KEY
(
[TeamID]
) REFERENCES [dbo].[Team] (
[TeamID]
)
GO
ALTER TABLE [dbo].[Salary] ADD
CONSTRAINT [FK_Salary_Client] FOREIGN KEY
(
[ClientID]
) REFERENCES [dbo].[Client] (
[ClientID]
),
CONSTRAINT [FK_Salary_Team] FOREIGN KEY
(
[TeamID]
) REFERENCES [dbo].[Team] (
[TeamID]
)
GO
TIA,
ChrisR
Comments inline...
David Portas
SQL Server MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:3BC29E40-C32D-44CE-8C0C-4778156B27DF@.microsoft.com...
> My disclaimer:
> I am by no means a design expert. My goal here is to learn, not criticize.
> With that out of the way... Im confused. I was just reading this article:
> http://www.sqlservercentral.com/colu...malization.asp
> Thank you BKelly, and again Im just trying to understand here. I have
> several questions about the end result.
> 1; If I want to query to see all football players, I have to query Sports,
> Team, Contract, and Clients. Mostly on character data types. Would it make
> more sense to have a lookup table as in my DDL below. This would probably
> be
> a very common query, and it seems that this would be a faster way.
Perhaps. But you've missed out some vital constraints. For example your
design allows the same agent to be entered multiple times. IDENTITY is not
an integrity constraint so it shouldn't be the only key of any table. BTW I
try to avoid the term "lookup table" when discussing logical design. I think
that term misleads the inexperienced into thinking that those tables are
somehow inherently different to others. Logically, there is only one type of
table in an RDBMS.

> 2; What if an Agent wants to represent more than 1 Sport?
> 3; What if a Sport has more than 1 Agent?
Then you have a many-to-many relationship. To implement that use a "joining
table" that contains the keys from both Agent and Sport.

> 4; Isnt LName, FName in the same column a violation of something?
Not formally a violation of NF, no. But if you need to represent that
element of data somewhere as two separate values then it probably makes
sense to define them as separate attributes.

> 5; In my design, Sport can be type-oed in the Team table. Would it be
> better
> for me to have a Sport table with a SportID column, and relationships on
> that
> column?
It would be better to create a *constraint* to enforce the business rule
that only valid sports can be entered. That could be a check constraint or
more likely a foreign key constraint to a separate Sport table. Creating a
"sportid" as a surrogate key is irrelevant to enforcing the business rule -
it's purely a physical model issue.

> 6; Again in my design, if I want to see all the Teams an Agent represents
> (probably a common query), I need to go through several tables. Would I be
> better off to have a lookup table between them?
Don't see how it would help the query but it's probably a better design
because of 2 and 3 above.

> 7; Are most of these questions really just dependant on the requirements,
> which obviously we dont know all of?
YES

> 8; Am I just way out in left field with my design?
>
You are taking the wrong approach to learning this. Get a book and learn the
basics of the Relational Model first. Then you will be ready to start
applying your knowledge to SQL tables. Otherwise you are like a
mathematician trying to teach yourself algebra by playing with a pocket
calculator.

> What I did here was designed the way I think it should be, and am hoping
> to
> get some constructive criticism as well as answerrs to the above
> questions.
> All points, good and bad, are welcomed. DDL below. Again, to learn here is
> my
> only aim.
> BE NICE JOE CELKO!
>
>

normalization questions

My disclaimer:
I am by no means a design expert. My goal here is to learn, not criticize.
With that out of the way... Im confused. I was just reading this article:
http://www.sqlservercentral.com/col...rmalization.asp
Thank you BKelly, and again Im just trying to understand here. I have
several questions about the end result.
1; If I want to query to see all football players, I have to query Sports,
Team, Contract, and Clients. Mostly on character data types. Would it make
more sense to have a lookup table as in my DDL below. This would probably be
a very common query, and it seems that this would be a faster way.
2; What if an Agent wants to represent more than 1 Sport?
3; What if a Sport has more than 1 Agent?
4; Isnt LName, FName in the same column a violation of something?
5; In my design, Sport can be type-oed in the Team table. Would it be better
for me to have a Sport table with a SportID column, and relationships on tha
t
column?
6; Again in my design, if I want to see all the Teams an Agent represents
(probably a common query), I need to go through several tables. Would I be
better off to have a lookup table between them?
7; Are most of these questions really just dependant on the requirements,
which obviously we dont know all of?
8; Am I just way out in left field with my design?
What I did here was designed the way I think it should be, and am hoping to
get some constructive criticism as well as answerrs to the above questions.
All points, good and bad, are welcomed. DDL below. Again, to learn here is m
y
only aim.
BE NICE JOE CELKO!
use [SportsAgency]
GO
CREATE TABLE [dbo].[Agent] (
[AgentId] [int] IDENTITY (1, 1) NOT NULL ,
[AgentLName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[AgentFName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Client] (
[ClientID] [int] IDENTITY (1, 1) NOT NULL ,
[ClientLName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NO
T NULL ,
[ClientFName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NO
T NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[LKPClientAgent] (
[ClientID] [int] NOT NULL ,
[AgentId] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[LKPClientTeam] (
[ClientID] [int] NOT NULL ,
[TeamID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Salary] (
[ClientID] [int] NOT NULL ,
[TeamID] [int] NOT NULL ,
[YearlySalary] [money] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Team] (
[TeamID] [int] IDENTITY (1, 1) NOT NULL ,
[TeamName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL ,
[Sport] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Agent] WITH NOCHECK ADD
CONSTRAINT [PK_Agent] PRIMARY KEY CLUSTERED
(
[AgentId]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Client] WITH NOCHECK ADD
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[ClientID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LKPClientAgent] WITH NOCHECK ADD
CONSTRAINT [PK_LKPClientAgent] PRIMARY KEY CLUSTERED
(
[ClientID],
[AgentId]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LKPClientTeam] WITH NOCHECK ADD
CONSTRAINT [PK_LKPClientTeam] PRIMARY KEY CLUSTERED
(
[ClientID],
[TeamID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Salary] WITH NOCHECK ADD
CONSTRAINT [PK_Salary] PRIMARY KEY CLUSTERED
(
[ClientID],
[TeamID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Team] WITH NOCHECK ADD
CONSTRAINT [PK_Team] PRIMARY KEY CLUSTERED
(
[TeamID]
) ON [PRIMARY]
GO
CREATE INDEX [IX_Client] ON [dbo].[Client]([ClientID]) ON &
#91;PRIMARY]
GO
ALTER TABLE [dbo].[LKPClientAgent] ADD
CONSTRAINT [FK_LKPClientAgent_Agent] FOREIGN KEY
(
[AgentId]
) REFERENCES [dbo].[Agent] (
[AgentId]
),
CONSTRAINT [FK_LKPClientAgent_Client] FOREIGN KEY
(
[ClientID]
) REFERENCES [dbo].[Client] (
[ClientID]
)
GO
ALTER TABLE [dbo].[LKPClientTeam] ADD
CONSTRAINT [FK_LKPClientTeam_Client] FOREIGN KEY
(
[ClientID]
) REFERENCES [dbo].[Client] (
[ClientID]
),
CONSTRAINT [FK_LKPClientTeam_Team] FOREIGN KEY
(
[TeamID]
) REFERENCES [dbo].[Team] (
[TeamID]
)
GO
ALTER TABLE [dbo].[Salary] ADD
CONSTRAINT [FK_Salary_Client] FOREIGN KEY
(
[ClientID]
) REFERENCES [dbo].[Client] (
[ClientID]
),
CONSTRAINT [FK_Salary_Team] FOREIGN KEY
(
[TeamID]
) REFERENCES [dbo].[Team] (
[TeamID]
)
GO
TIA,
ChrisRComments inline...
David Portas
SQL Server MVP
--
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:3BC29E40-C32D-44CE-8C0C-4778156B27DF@.microsoft.com...
> My disclaimer:
> I am by no means a design expert. My goal here is to learn, not criticize.
> With that out of the way... Im confused. I was just reading this article:
> http://www.sqlservercentral.com/col...rmalization.asp
> Thank you BKelly, and again Im just trying to understand here. I have
> several questions about the end result.
> 1; If I want to query to see all football players, I have to query Sports,
> Team, Contract, and Clients. Mostly on character data types. Would it make
> more sense to have a lookup table as in my DDL below. This would probably
> be
> a very common query, and it seems that this would be a faster way.
Perhaps. But you've missed out some vital constraints. For example your
design allows the same agent to be entered multiple times. IDENTITY is not
an integrity constraint so it shouldn't be the only key of any table. BTW I
try to avoid the term "lookup table" when discussing logical design. I think
that term misleads the inexperienced into thinking that those tables are
somehow inherently different to others. Logically, there is only one type of
table in an RDBMS.

> 2; What if an Agent wants to represent more than 1 Sport?
> 3; What if a Sport has more than 1 Agent?
Then you have a many-to-many relationship. To implement that use a "joining
table" that contains the keys from both Agent and Sport.

> 4; Isnt LName, FName in the same column a violation of something?
Not formally a violation of NF, no. But if you need to represent that
element of data somewhere as two separate values then it probably makes
sense to define them as separate attributes.

> 5; In my design, Sport can be type-oed in the Team table. Would it be
> better
> for me to have a Sport table with a SportID column, and relationships on
> that
> column?
It would be better to create a *constraint* to enforce the business rule
that only valid sports can be entered. That could be a check constraint or
more likely a foreign key constraint to a separate Sport table. Creating a
"sportid" as a surrogate key is irrelevant to enforcing the business rule -
it's purely a physical model issue.

> 6; Again in my design, if I want to see all the Teams an Agent represents
> (probably a common query), I need to go through several tables. Would I be
> better off to have a lookup table between them?
Don't see how it would help the query but it's probably a better design
because of 2 and 3 above.

> 7; Are most of these questions really just dependant on the requirements,
> which obviously we dont know all of?
YES

> 8; Am I just way out in left field with my design?
>
You are taking the wrong approach to learning this. Get a book and learn the
basics of the Relational Model first. Then you will be ready to start
applying your knowledge to SQL tables. Otherwise you are like a
mathematician trying to teach yourself algebra by playing with a pocket
calculator.

> What I did here was designed the way I think it should be, and am hoping
> to
> get some constructive criticism as well as answerrs to the above
> questions.
> All points, good and bad, are welcomed. DDL below. Again, to learn here is
> my
> only aim.
> BE NICE JOE CELKO!
>
>

Noob questions about Reporting Services

Sorry for the noobish questions, but I just loaded Reporting Services and am trying to learn about it.

1. Is there a way to manipulate the tool bar that appears with the report? The one that has the pages, percent view, find/next, format/export and refresh. I can't seem to find any properties to set for it. For example, when I created reports with the evaluation copy, a print button would be on the toolbar, but with the full version, the reports I create don't have the print button.

2. For report parameters, is there a way to have a text box that users can input into rather than using dropdowns?

3. Does using the refresh button on the report toolbar refresh the dataset used for the report? Or does the user have to exit the report and have it recreated?

4. How do I use a parameter in a report title? For example, if I wanted to have a report where the user selected a month for a report and the parameter name was MONTH, I'd want the title of the report to show up as MONTH Report.

Thanks for any help.

Addendum: Also, I've read through some of the threads in here and read references to a client report builder. Is there a separate client report builder or is it just the add-in to Visual Studio?

Hello jwilliams:
I've worked with this RS thing for 4 months. I don't have much experience but I'll try to answer:

1.- No, as far as I know
2.- Yes, when you define the parameter in the Report>Report Parameters... menu you get a dialog window. There, for each parameter, you can specify if you want to get the values from a Query or as an user input. Just select the "Non-Queried" option in the "Available values" section. Be careful: the user might enter an incorrect value without notice it (and soy you may recieve complains like "hey, this report doesn't work")
3.- Yes, clicking the refresh button is enough (as far as I know)
4.- I'll assume that you want to show the parameter value in a textbox. So, you want to concatenate an arbitray text and the parameter value. You can do this by typing something like this in the textbox:

="The value selected is: " & Parameters!ParameterName.Value

or, in the case you refer:
=Parameters!MONTH.Value & " Report"

I hope this helps.
Regards|||Thanks so much for your reply. Any idea on the client report builder? Is there one, or is there only the add-in for Visual Studio? Also, is there a way to add a column to your report once it's made? I used the wizard to generate my reports and on one, I wanted to add a column, but couldn't find where to do it.

Once again, thanks for your help.|||Well, I've just worked with the report designer, not with the report builder. See, Microsoft has just released some courses for free, and I guess you can find some helpful information there (in the bottom of the page there's a linkt to a free online RS course)

https://www.microsoftelearning.com/sqlserver2005/

Regards|||Thanks. As far as your answer to my 2nd question:

2.- Yes, when you define the parameter in the Report>Report Parameters... menu you get a dialog window. There, for each parameter, you can specify if you want to get the values from a Query or as an user input. Just select the "Non-Queried" option in the "Available values" section. Be careful: the user might enter an incorrect value without notice it (and soy you may recieve complains like "hey, this report doesn't work")

When I open the Report>Report Parameters and select the Non-Queried option, it brings up a Label and Value table to allow you to set up a drop down box, but I don't see anywhere that allows you just to specify a text box with a prompt for the user to input into. Am I missing something?

Also, I noticed that in another thread where someone is trying to pass a wildcard in a parameter--I'm trying to do that also, but can't figure out how. (I want the user to select a month or all to run the report on). You provided this if statement:

IF @.param = -5
BEGIN
SELECT *
FROM tbl_employee
END
ELSE
BEGIN
SELECT *
FROM tbl_employee
WHERE id_boss = @.param
END

Where does this statement go? In the table data filters property? That's where I put all my parameters for the report.|||OK, about the 2nd answer: just leave the table empty. This forces the user to write a parameter value in a textbox (if you fill the table then a dropdown list will appear in the report)

About the other one: my scenario is the following.
I had a parameterized report. There's only one parameter (called @.id_acm) and the values are taken from a query (in the report>report parameters... menu I choose "From Query" instead of "non-queried" and so a drop down list is shown to the user) The source query for the parameter values looked like this:

SELECT id_acm AS param_value, nombre AS param_label
FROM tbl_acm

And the query used to get the information for the report (the one I wrote in the DataSet) looked like this:
SELECT *
FROM tbl_sales S
INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acm
WHERE acm.id_acm = @.id_acm

I also did NOT specified any default value for the parameter.

When my boss executed the report he noticed that it showed anything until he selected a value from the drop down list. My boss told me that he wanted to see all the information by default, and if he needed information about a specific person he would select the person from the drop down list. Here comes the tricky part.
I noticed that all the current (and future) values for the id_acm field in the tbl_acm table were greater than 0.
So I modified the source query for the parameter like this:

SELECT -5 AS param_value, 'Everyone' AS param_label
UNION
SELECT id_acm AS param_value, nombre AS param_label
FROM tbl_acm

And I specified a default value of -5 for the @.id_acm parameter.
Also, I modified the principal query like this:

IF @.id_acm = -5
BEGIN
SELECT *
FROM tbl_sales S
INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acb
END ELSE
BEGIN
SELECT *
FROM tbl_sales S
INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acb
WHERE acm.id_acm = @.id_acm
END

Let me explain this: when the user selects "Everyone" the principal query returns the information of all the persons (because there's no WHERE clause)
If the user selects a specific name, then the principal query returns only that person's information (because there's a WHERE clause filtering the data)
By default the paramter value is -5 and so the report shows everyone's information.
Of course, if you have 2 parameters you'll have to nest 2 IF statemens. In fact, if you have n parameters you'll have to nest n IF statemens and you'll get 2^n posibilities for the WHERE clauses (that's the big pain)
I don't know if this helps in your scenario, but I hope it does.