Normalized Data base

I have been arguing with my partner if we shoud or not design a normalized
data base or denormalized. How, can we know when we should choose one or the
other?
Thanks a lot!
Lina,
I assume you are referring to designing a data warehouse since you are
posting in this group. There are three basic ways of designing the model.
Third Normal Form - This is the Inmon approach and works well data mining
and building down stream data marts. It's draw backs are usually higher
development, hardware, and software costs. It's should be used for large
data warehouses with multiple sources.
Star Schema - A special from of denormalized schema. This is the Kimbal
approach. It good in situations of combining multiple sources and the use
is intended to be more direct user access.
Denormalized - This should be used in data warehouses that really only have
one significant source and the warehouse will be used from more direct
reporting than anything else. It's relatively quick simple and cheap but
has issues with integration.
Whichever approach you choose to take realize that once reports and other
uses are written against it, changing the general approach is expensive.
Just opinions...
"Lina Manjarres" <LinaManjarres@.discussions.microsoft.com> wrote in message
news:A3CF8EA4-FAB4-4831-8BBF-4AFBCD41F5C6@.microsoft.com...
>I have been arguing with my partner if we shoud or not design a normalized
> data base or denormalized. How, can we know when we should choose one or
> the
> other?
> Thanks a lot!
|||I guess it depends on what type of denormalization. The more denormalized a
datawarehouse model is, the harder it will be to keep the data consistent as
new data is inserted or updated incrementally from the transactional
database. For example, if a person's zip code changes or a product is
associated with a new category, these changes must be updated in multiple
locations. However, if the datawarehouse is reloaded from scratch
periodically (similar to a reporting database), then it is less of an issue.
"Lina Manjarres" <LinaManjarres@.discussions.microsoft.com> wrote in message
news:A3CF8EA4-FAB4-4831-8BBF-4AFBCD41F5C6@.microsoft.com...
> I have been arguing with my partner if we shoud or not design a normalized
> data base or denormalized. How, can we know when we should choose one or
the
> other?
> Thanks a lot!
|||Hi Lina,
if you go to my downloads page on www.peternolan.com there are some
presentations, one of which is about data modeling and some newsletters
one of which is about data modeling.
There is something of a 'reglious' debate over modeling techniques in
DWing that has been going on for many years. I put my public opinion on
my FAQs page...
Current 'best practice' of publicly available information is to have an
archive layer in 3NF + Time variant model (plus stability analysis if
you need it) as well as an analytical later (dimensional). This is also
the most expensive practice......not everyone needs an archive layer
as described here...
The 'next generation' models I work with make the separate archive
layer redundant...I've written on this topic many times and the posts
are archived on http://www.datawarehousing.com/list.asp.
Best Regards
Peter Nolan
www.peternolan.com
sql

Normalized Data base

I have been arguing with my partner if we shoud or not design a normalized
data base or denormalized. How, can we know when we should choose one or the
other?
Thanks a lot!Lina,
I assume you are referring to designing a data warehouse since you are
posting in this group. There are three basic ways of designing the model.
Third Normal Form - This is the Inmon approach and works well data mining
and building down stream data marts. It's draw backs are usually higher
development, hardware, and software costs. It's should be used for large
data warehouses with multiple sources.
Star Schema - A special from of denormalized schema. This is the Kimbal
approach. It good in situations of combining multiple sources and the use
is intended to be more direct user access.
Denormalized - This should be used in data warehouses that really only have
one significant source and the warehouse will be used from more direct
reporting than anything else. It's relatively quick simple and cheap but
has issues with integration.
Whichever approach you choose to take realize that once reports and other
uses are written against it, changing the general approach is expensive.
Just opinions...
"Lina Manjarres" <LinaManjarres@.discussions.microsoft.com> wrote in message
news:A3CF8EA4-FAB4-4831-8BBF-4AFBCD41F5C6@.microsoft.com...
>I have been arguing with my partner if we shoud or not design a normalized
> data base or denormalized. How, can we know when we should choose one or
> the
> other?
> Thanks a lot!|||I guess it depends on what type of denormalization. The more denormalized a
datawarehouse model is, the harder it will be to keep the data consistent as
new data is inserted or updated incrementally from the transactional
database. For example, if a person's zip code changes or a product is
associated with a new category, these changes must be updated in multiple
locations. However, if the datawarehouse is reloaded from scratch
periodically (similar to a reporting database), then it is less of an issue.
"Lina Manjarres" <LinaManjarres@.discussions.microsoft.com> wrote in message
news:A3CF8EA4-FAB4-4831-8BBF-4AFBCD41F5C6@.microsoft.com...
> I have been arguing with my partner if we shoud or not design a normalized
> data base or denormalized. How, can we know when we should choose one or
the
> other?
> Thanks a lot!|||Hi Lina,
if you go to my downloads page on www.peternolan.com there are some
presentations, one of which is about data modeling and some newsletters
one of which is about data modeling.
There is something of a 'reglious' debate over modeling techniques in
DWing that has been going on for many years. I put my public opinion on
my FAQs page...
Current 'best practice' of publicly available information is to have an
archive layer in 3NF + Time variant model (plus stability analysis if
you need it) as well as an analytical later (dimensional). This is also
the most expensive practice......not everyone needs an archive layer
as described here...
The 'next generation' models I work with make the separate archive
layer redundant...I've written on this topic many times and the posts
are archived on http://www.datawarehousing.com/list.asp.
Best Regards
Peter Nolan
www.peternolan.com

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!
>
>

Normalisation - Query

Hi all,
I need to design apet database whee customers get regeistered and then pst their pet deals. They are either for sale or just rehoming.

Can someone please take a few moments to have a look at what I have done so far and tell me if it is any good.

This is my 6th time re-designing the tables.

Many thanks.

ps: owners can be either Private or part of an Organisation.

Customer table:
Customer#, C_Fname, C_Lname, C.address1, C.address2, C.postCode, C.fax
C.country, UKlocation, C.Hphone, C.Mphone, C.e-mail, (rehoming-services?
yes/no)

Organisation details:
customer#, Org#, Org.website, Org.AreaCoverd,
Org.activities, Org.addinfo, Org.Logo

Members of Org details:
Member#, Org#, Member_name, Member_email, member_Phone, member_ext.

Host table
customer#, host#, animals accepted?, max_no_days, price_per_day

Pets table:
pet#, picture, p_type, p_breed

Permenant pets for rehoming
pet#, pet_perm#, name, p.color, p.d_o_b, age,
p.size, p.personality1, p.personality2, p.personalty3, p.vaccination,
p. allergies, microchipped.

pets for sale:
Pet#, sale#, no.ofMales, PriceperMale, No.ofFemales, PriceperFemale,what is the difference between a customer and a member? can a customer be more than one member? can a member be more than one customer?

what is the difference between a customer and a host? can a customer be more than one host? can a host be more than one customer?

and i don't understand how you can have "number of males" attached to a given pet

do you have an entity-relationship diagram for this structure? that would really help you straighten out which foreign keys go where

see Data Modeling (http://www.utexas.edu/cc/database/datamodeling/) for an excellent tutorial

rudy
http://rudy.ca/

Non blocking query on a table in SQL Server 2000

Hi Folks,
How can I invoke a non blocking ODBC query on a table in a SQL Server
database. Our current design requires the method invoking the query to
'return' immediately after invoking the query. The results of the query
should be put in a globally accessible location.
I think SQLFetch is a blocking call.
Thanks,
Vishal
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Vishal,
You have to use muliple threads. It is SQL Execute that usually takes
long time, not SQLFetch(). A call to single SQLFetch usually does not
take long unless you have some network connection problem.
Here is what you can do:
From your main application thread spawn a new one that does
SQLExecute(). The same spawned thread will call SQLFetch and put the
result in a shared buffer, possibly a global to you app. Then send a
Window message to you application notifying it that some data has be put
in the buffer. Read this data from your primary thread.
Regards,
Elvis
Dead not so long ago.
Vishal Prabhu wrote:

quote:

---

> Hi Folks,
> How can I invoke a non blocking ODBC query on a table in a SQL Server
> database. Our current design requires the method invoking the query to
> 'return' immediately after invoking the query. The results of the query
> should be put in a globally accessible location.
> I think SQLFetch is a blocking call.
> Thanks,
> Vishal
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

---

Non blocking query call

Hi Folks,
How can I invoke a non blocking ODBC query on a table in a SQL Server
database. Our current design requires the method invoking the query to
'return' immediately after invoking the query. The results of the
query should be put in a globally accessible location.
I think SQLFetch is a blocking call.
Thanks,
VishalWhat you are looking for is called Asynchronous Execution. You must enable
async using SQLSetConnectAttr() and SQL_ATTR_ASYNC_ENABLE.
ODBC APIs will return SQL_STILL_EXECUTING while the operation is still
pending. Once the operation completes, the API will return something other
than SQL_STILL_EXECUTING (SQL_SUCCESS if the operation succeeded, etc).
The async model exposed by ODBC is a Poll model, where you have to
periodically poll for completion (instead of a Notification model, where you
are notified that the operation completed).
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Vishal Prabhu" <vprabhu@.uci.edu> wrote in message
news:31cf0289.0312191118.4354c4fd@.posting.google.com...

quote:

---

> Hi Folks,
> How can I invoke a non blocking ODBC query on a table in a SQL Server
> database. Our current design requires the method invoking the query to
> 'return' immediately after invoking the query. The results of the
> query should be put in a globally accessible location.
> I think SQLFetch is a blocking call.
> Thanks,
> Vishal

---