Normalization Tool

Anyone know if a tool exists that can be pointed at a specific database, analyze it, and report and/or perform normalization recommendations?

well you have to do it on your own.

its actually easy

|||Joey, I've done it for years and am comfortable with it. I was just wondering if such a tool existed.|||

I guess no such tool exist as of now.

maybe we should invent one

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

Hai everybody recently i came across this article and i have tried to answer all the follwoing questions. But i am not sure its correct or not..so you peoples can comment on the follwoing questions.

2)

Employee (ssn, Name, Salary, Address, ListOfSkills)

Yes,

No.Ans: No. as list of skills would be repeated.

3)

Department (Did, Dname, ssn)

Yes,

No.Ans: No. ssn and did should be moved to a seperate table.

4)

Vehicle (LicensePlate,Brand,

Model, PurchasePrice, Year, OwnerSSN, OwnerName

Yes,

NoAns: No.

5)

Employee (ssn, Name, Salary, did) (obs.:

employee can only belong to one department)

Yes,

No.Ans: Yes.

6)

Customer (Cust_Id, Name, Salesperson, Region) where Salesperson

determines Region.

Yes,

No.Ans: No.Salesperson and region should be moved to a seperate table.

7)

Component (ItemNo, ComponentNo, ItemName, Quantity) where ItemNo

->ItemName

Yes,

No.Ans: No.As itemname is a subset of itemno and not a subset of both itemno and componentno.

Not homework, right? :)

Hai everybody recently i came across this article and i have tried to answer all the follwoing questions. But i am not sure its correct or not..so you peoples can comment on the follwoing questions.

2) Employee (ssn, Name, Salary, Address, ListOfSkills)

Yes, No. Ans: No. as list of skills would be repeated.

louis: exactly. Any column that is plural likely represents multiple things...

3) Department (Did, Dname, ssn)

Yes, No. Ans: No. ssn and did should be moved to a seperate table.

Louis: Well, Did is fine, but I would expect that ssn violates fourth normal form. If the SSN represents something where there is only one of them (like the manager,) then this is fine. If it represents a member of a department, then you definitely have problems because the department name and members of the department relate differently to the Did key of the Department table.

4) Vehicle (LicensePlate, Brand, Model, PurchasePrice, Year, OwnerSSN, OwnerName

Yes, No Ans: No.

Louis: if you are only allowing a single owner of the vehicle AND you only track the most recent purchase information, then yes. Else no. You always need to consider cardinality between attribute and key.

5) Employee (ssn, Name, Salary, did) (obs.: employee can only belong to one department)

Yes, No. Ans: Yes.

Louis: agree. One employee, one name, one salary, one department, all data corresponds to the employee. That is fine.

6) Customer (Cust_Id, Name, Salesperson, Region) where Salesperson determines Region.

Yes, No. Ans: No.Salesperson and region should be moved to a seperate table.

Louis: Good question. Was this the salesperson of the customer, and the Region of the customer? Or is this the region that the salesperson works, regardless of the location of the customer? That makes a big different.

7) Component (ItemNo, ComponentNo, ItemName, Quantity) where ItemNo -> ItemName

Yes, No. Ans: No.As itemname is a subset of itemno and not a subset of both itemno and componentno.

Louis. No, like you said, this violates second normal form

|||Thanks louis, definetly its not homework. I am very much interested in design, that's why i posted.sql

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

Normalization Question....

Hi,
I have a table of entities. Each of which has a unique ID. For a certain
subset of those entities, a particular set of values can apply to a pair of
entities.
Let's call the entity ID's E1 and E2 and the associated values x, y and z.
E1 and E2 are GUID's and x, y and z are floats.
My temptation is to create a table that has E1 and E2 as the primary key and
have columns for x, y and z. However, since E1 and E2 both represent an ID
column that relate to the same table, I feel I am breaking the first normal
form.
Essentially what needs to happen is that a pair of entity ID's needs to be
related to x, y and z values and I want the table normalized. Any advice
would be appreciated. Thanks in advance.
-PetePete Wittig wrote:
> Hi,
> I have a table of entities. Each of which has a unique ID. For a certain
> subset of those entities, a particular set of values can apply to a pair o
f
> entities.
> Let's call the entity ID's E1 and E2 and the associated values x, y and z.
> E1 and E2 are GUID's and x, y and z are floats.
> My temptation is to create a table that has E1 and E2 as the primary key a
nd
> have columns for x, y and z. However, since E1 and E2 both represent an I
D
> column that relate to the same table, I feel I am breaking the first norma
l
> form.
> Essentially what needs to happen is that a pair of entity ID's needs to be
> related to x, y and z values and I want the table normalized. Any advice
> would be appreciated. Thanks in advance.
> -Pete
It is extremely difficult to give goosd advise on such questions
without the opportunity to analyse your requirements in detail. Here
are some ideas. If you are saying that (x,y,z) uniquely defines a set
of entities then you can implement a cascading foreign key:
CREATE TABLE foo (x FLOAT NOT NULL, y FLOAT NOT NULL, z FLOAT NOT NULL,
PRIMARY KEY (x,y,z));
CREATE TABLE entities (e UNIQUEIDENTIFIER PRIMARY KEY, x FLOAT NOT
NULL, y FLOAT NOT NULL, z FLOAT NOT NULL, FOREIGN KEY (x,y,z)
REFERENCES foo (x,y,z) ON UPDATE CASCADE);
Perhaps more likely though is that your set is defined by some other
attribute(s) you haven't mentioned (foo_key in this example):
CREATE TABLE foo (foo_key INTEGER NOT NULL PRIMARY KEY, x FLOAT NOT
NULL, y FLOAT NOT NULL, z FLOAT NOT NULL);
CREATE TABLE entities (e UNIQUEIDENTIFIER PRIMARY KEY, foo_key /* ?
*/ INTEGER NOT NULL REFERENCES foo (foo_key));
Your choice of datatypes would make me want to review this design. I've
almost never used FLOAT in tables. Although it certainly has legitimate
uses, most of the time the exact types (INTEGER or DECIMAL for example)
are much more useful. FLOAT probably isn't a good choice for a key
because of its imprecise nature. I have difficulty making sense of your
business requirement if you don't have the additional key I proposed
for the "foo" table but then I don't know your business...
Also, UNIQUEIDENTIFIER, while it may be used as a PK isn't generally
good as the only key of a table. Certainly it shouldn't be so if its
purpose is an artificial surrogate.
David Portas
SQL Server MVP
--|||Hi David,
Thank you for the response. Allow me to clarify: I have a table that
describes my entities (call it Table E). E contains 1...n entries.
CREATE TABLE E (E_key UNIQUEIDENTIFIER PRIMARY KEY, Name nvarchar(100) NOT
NULL);
The other table I was tempted to create would have looked like this:
CREATE TABLE Params (E1 UNIQUEIDENTIFIER, E2 UNIQUEIDENTIFIER, X FLOAT NOT
NULL, Y FLOAT NOT NULL, Z FLOAT NOT NULL,
PRIMARY KEY (E1, E2))
Where E1 and E2 would have been entries from Table E. So certain pairs of
entities (i.e. entry 5 and 2, entry 19 and 20) have parameters X, Y and Z.
X, Y and Z are dependent on the pair of entities.
If I understand you correctly, I think this method would work for me best:
"CREATE TABLE foo (foo_key INTEGER NOT NULL PRIMARY KEY, x FLOAT NOT
NULL, y FLOAT NOT NULL, z FLOAT NOT NULL);
CREATE TABLE entities (e UNIQUEIDENTIFIER PRIMARY KEY, foo_key /* ?
*/ INTEGER NOT NULL REFERENCES foo (foo_key));"
However, allow me ask a few questions for clarification. In this case,
foo_key is an arbitrary key (i.e. an autonumber or a GUID)?
Thanks again.
BTW: My reasoning for using floats is because I am storing math/scientific
data so I need a high level of precision.
-Pete
"David Portas" wrote:

> Pete Wittig wrote:
> It is extremely difficult to give goosd advise on such questions
> without the opportunity to analyse your requirements in detail. Here
> are some ideas. If you are saying that (x,y,z) uniquely defines a set
> of entities then you can implement a cascading foreign key:
> CREATE TABLE foo (x FLOAT NOT NULL, y FLOAT NOT NULL, z FLOAT NOT NULL,
> PRIMARY KEY (x,y,z));
> CREATE TABLE entities (e UNIQUEIDENTIFIER PRIMARY KEY, x FLOAT NOT
> NULL, y FLOAT NOT NULL, z FLOAT NOT NULL, FOREIGN KEY (x,y,z)
> REFERENCES foo (x,y,z) ON UPDATE CASCADE);
> Perhaps more likely though is that your set is defined by some other
> attribute(s) you haven't mentioned (foo_key in this example):
> CREATE TABLE foo (foo_key INTEGER NOT NULL PRIMARY KEY, x FLOAT NOT
> NULL, y FLOAT NOT NULL, z FLOAT NOT NULL);
> CREATE TABLE entities (e UNIQUEIDENTIFIER PRIMARY KEY, foo_key /* ?
> */ INTEGER NOT NULL REFERENCES foo (foo_key));
> Your choice of datatypes would make me want to review this design. I've
> almost never used FLOAT in tables. Although it certainly has legitimate
> uses, most of the time the exact types (INTEGER or DECIMAL for example)
> are much more useful. FLOAT probably isn't a good choice for a key
> because of its imprecise nature. I have difficulty making sense of your
> business requirement if you don't have the additional key I proposed
> for the "foo" table but then I don't know your business...
> Also, UNIQUEIDENTIFIER, while it may be used as a PK isn't generally
> good as the only key of a table. Certainly it shouldn't be so if its
> purpose is an artificial surrogate.
> --
> David Portas
> SQL Server MVP
> --
>|||Pete Wittig wrote:
> Hi David,
> Thank you for the response. Allow me to clarify: I have a table that
> describes my entities (call it Table E). E contains 1...n entries.
> CREATE TABLE E (E_key UNIQUEIDENTIFIER PRIMARY KEY, Name nvarchar(100) NOT
> NULL);
> The other table I was tempted to create would have looked like this:
> CREATE TABLE Params (E1 UNIQUEIDENTIFIER, E2 UNIQUEIDENTIFIER, X FLOAT NOT
> NULL, Y FLOAT NOT NULL, Z FLOAT NOT NULL,
> PRIMARY KEY (E1, E2))
> Where E1 and E2 would have been entries from Table E. So certain pairs of
> entities (i.e. entry 5 and 2, entry 19 and 20) have parameters X, Y and Z.
> X, Y and Z are dependent on the pair of entities.
> If I understand you correctly, I think this method would work for me best:
> "CREATE TABLE foo (foo_key INTEGER NOT NULL PRIMARY KEY, x FLOAT NOT
> NULL, y FLOAT NOT NULL, z FLOAT NOT NULL);
> CREATE TABLE entities (e UNIQUEIDENTIFIER PRIMARY KEY, foo_key /* ?
> */ INTEGER NOT NULL REFERENCES foo (foo_key));"
> However, allow me ask a few questions for clarification. In this case,
> foo_key is an arbitrary key (i.e. an autonumber or a GUID)?
> Thanks again.
> BTW: My reasoning for using floats is because I am storing math/scientific
> data so I need a high level of precision.
> -Pete
>
Your choice of keys in the Params table looks very suspect to me. If E1
or E2 can appear multiple times then apparently these are groups, not
pairs. I had undestood that E1 determined (x,y,z) and that E2
determined the SAME (x,yz). If so, your table creates redundancy
because you allow E1 or E2 to be added multiple times with different
values of (x,y,z). Based on your original post I would have guessed:
CREATE TABLE Params (E1 UNIQUEIDENTIFIER NOT NULL PRIMARY KEY
REFERENCES e (e_key), E2 UNIQUEIDENTIFIER NOT NULL UNIQUE REFERENCES e
(e_key), x FLOAT NOT NULL, y FLOAT NOT NULL, z FLOAT NOT NULL)
but now I have my doubts as to whether I understood you.
Design-by-newsgroup is a recipe for these kinds of misunderstandings
unfortunately.

> CREATE TABLE entities (e UNIQUEIDENTIFIER PRIMARY KEY, foo_key /* ?
> */ INTEGER NOT NULL REFERENCES foo (foo_key));"
> However, allow me ask a few questions for clarification. In this case,
> foo_key is an arbitrary key (i.e. an autonumber or a GUID)?
I doubt it, but you tell me. I was guessing there might be some
meaningful attribute that relates pairs or sets of entities together.
You wouldn't (shouldn't) need to use an artificial key unless (x,y,z)
are unique in foo.
David Portas
SQL Server MVP
--|||Thanks for the reply. Let me try and put this into context. In my
application, I want to look up an entity. That entity has certain attribute
s
which are returned such as its name. Another type of information is its
interaction parameters, how it interacts with other entities. This is the
data that I am trying to store.
So for a entity 1, I would return its name. Then I would want to return all
the ID's of the other entities it interacts with as well as the x, y and z
parameters associated with each particular pair/interaction (so potentially,
entity 1 & entity 2 and their associated x, y and z, entity 1 & entity 5 and
their associated x, y and z...).
Additionally, I will have a tool in which I can enter two entities and
return their x, y and z parameters.
In the application I will have the ID for entity 1, what I need to get is
all entity ID's that have an interaction with entity 1 and the associated x,
y and z parameters for that interaction.
I think you are correct in saying the key in my table Params looks suspect.
I agree. Since the x, y and z parameters would be the same for the pairs
entity1 & entity 2 and entity 2 & entity 1.
Does this help to clarify?
"David Portas" wrote:

> Pete Wittig wrote:
> Your choice of keys in the Params table looks very suspect to me. If E1
> or E2 can appear multiple times then apparently these are groups, not
> pairs. I had undestood that E1 determined (x,y,z) and that E2
> determined the SAME (x,yz). If so, your table creates redundancy
> because you allow E1 or E2 to be added multiple times with different
> values of (x,y,z). Based on your original post I would have guessed:
> CREATE TABLE Params (E1 UNIQUEIDENTIFIER NOT NULL PRIMARY KEY
> REFERENCES e (e_key), E2 UNIQUEIDENTIFIER NOT NULL UNIQUE REFERENCES e
> (e_key), x FLOAT NOT NULL, y FLOAT NOT NULL, z FLOAT NOT NULL)
> but now I have my doubts as to whether I understood you.
> Design-by-newsgroup is a recipe for these kinds of misunderstandings
> unfortunately.
>
> I doubt it, but you tell me. I was guessing there might be some
> meaningful attribute that relates pairs or sets of entities together.
> You wouldn't (shouldn't) need to use an artificial key unless (x,y,z)
> are unique in foo.
> --
> David Portas
> SQL Server MVP
> --
>|||Pete Wittig wrote:

> Thanks for the reply. Let me try and put this into context. In my
> application, I want to look up an entity. That entity has certain attribu
tes
> which are returned such as its name. Another type of information is its
> interaction parameters, how it interacts with other entities. This is the
> data that I am trying to store.
> So for a entity 1, I would return its name. Then I would want to return a
ll
> the ID's of the other entities it interacts with as well as the x, y and z
> parameters associated with each particular pair/interaction (so potentiall
y,
> entity 1 & entity 2 and their associated x, y and z, entity 1 & entity 5 a
nd
> their associated x, y and z...).
> Additionally, I will have a tool in which I can enter two entities and
> return their x, y and z parameters.
> In the application I will have the ID for entity 1, what I need to get is
> all entity ID's that have an interaction with entity 1 and the associated
x,
> y and z parameters for that interaction.
> I think you are correct in saying the key in my table Params looks suspect
.
> I agree. Since the x, y and z parameters would be the same for the pairs
> entity1 & entity 2 and entity 2 & entity 1.
> Does this help to clarify?
>
>
If we are only talking about pairs of entities then I think we're
nearly there. Just add the constraint E1<E2 so that you can never get
redundant pairs (A-B and B-A):
CREATE TABLE Params (E1 UNIQUEIDENTIFIER NOT NULL PRIMARY KEY
REFERENCES e (e_key), E2 UNIQUEIDENTIFIER NOT NULL UNIQUE REFERENCES e
(e_key), CHECK (e1 < e2), x FLOAT NOT NULL, y FLOAT NOT NULL, z FLOAT
NOT NULL);
BTW I have left out the constraint names for brevity. You should always
give constraints your own names rather than let the system generate
them. It makes them much easier to maintain.
David Portas
SQL Server MVP
--|||Thanks for the reply.
In regards to this table:
CREATE TABLE Params (E1 UNIQUEIDENTIFIER NOT NULL PRIMARY KEY
REFERENCES e (e_key), E2 UNIQUEIDENTIFIER NOT NULL UNIQUE REFERENCES e
(e_key), CHECK (e1 < e2), x FLOAT NOT NULL, y FLOAT NOT NULL, z FLOAT
NOT NULL);
If I read this table correctly, it has only E1 as the primary key. I
believe that the primary key will have to be E1 and E2 since I could have
values like this (I've substituted ints for unique identifiers for
convenience in this example):
E1 E2 x y z
-- -- -- -- --
1 5 0.1 0.2 0.3
1 6 0.4 0.5 0.6
9 1 0.7 0.8 0.9
If I make the addition of including E2 in the primary key, will that effect
the "CHECK (e1 < e2)"?
I still have a question with regards to normalization. Since there is an E1
and E2 column in the Params table, does this mean it violates the 1NF? If
not, could you please explain why?
Thanks again.
"David Portas" wrote:

> Pete Wittig wrote:
>
> If we are only talking about pairs of entities then I think we're
> nearly there. Just add the constraint E1<E2 so that you can never get
> redundant pairs (A-B and B-A):
> CREATE TABLE Params (E1 UNIQUEIDENTIFIER NOT NULL PRIMARY KEY
> REFERENCES e (e_key), E2 UNIQUEIDENTIFIER NOT NULL UNIQUE REFERENCES e
> (e_key), CHECK (e1 < e2), x FLOAT NOT NULL, y FLOAT NOT NULL, z FLOAT
> NOT NULL);
> BTW I have left out the constraint names for brevity. You should always
> give constraints your own names rather than let the system generate
> them. It makes them much easier to maintain.
> --
> David Portas
> SQL Server MVP
> --
>|||Pete Wittig wrote:
> E1 E2 x y z
> -- -- -- -- --
> 1 5 0.1 0.2 0.3
> 1 6 0.4 0.5 0.6
> 9 1 0.7 0.8 0.9
> If I make the addition of including E2 in the primary key, will that effec
t
> the "CHECK (e1 < e2)"?
Still looks OK to me.

> I still have a question with regards to normalization. Since there is an
E1
> and E2 column in the Params table, does this mean it violates the 1NF? If
> not, could you please explain why?
>
First Normal Form is an elusive concept. Strictly speaking any table
conforms to 1NF if it has a candidate key and doesn't permit nulls. In
practice we look out for attributes that contain more than one domain
of values or have several columns representing the same attribute or
contain data structures encoded in strings. Loosely speaking we say
that these are a violation of 1NF. These are all subjective notions.
Sometimes it seems "obvious" when the spirit of 1NF is being violated.
Sometimes it is more tricky and the judgement will come down to one's
knowledge and experience of what works and what doesn't.
In your case we can say that using a pair of foreign keys in a single
table to represent a many-to-many relationship is a proven design that
appears frequently and that almost every database architect must have
used it. There are alternatives but the major benefit of your design is
that it's very easy to apply the necessary constraints for the business
rules. Your table is in 1NF and I don't see a better design.
David Portas
SQL Server MVP
--|||Thanks very much David. I appreciate the advice.
"David Portas" wrote:

> Pete Wittig wrote:
> Still looks OK to me.
>
> First Normal Form is an elusive concept. Strictly speaking any table
> conforms to 1NF if it has a candidate key and doesn't permit nulls. In
> practice we look out for attributes that contain more than one domain
> of values or have several columns representing the same attribute or
> contain data structures encoded in strings. Loosely speaking we say
> that these are a violation of 1NF. These are all subjective notions.
> Sometimes it seems "obvious" when the spirit of 1NF is being violated.
> Sometimes it is more tricky and the judgement will come down to one's
> knowledge and experience of what works and what doesn't.
> In your case we can say that using a pair of foreign keys in a single
> table to represent a many-to-many relationship is a proven design that
> appears frequently and that almost every database architect must have
> used it. There are alternatives but the major benefit of your design is
> that it's very easy to apply the necessary constraints for the business
> rules. Your table is in 1NF and I don't see a better design.
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks for your help David. I appreciate it.
"David Portas" wrote:

> Pete Wittig wrote:
> Still looks OK to me.
>
> First Normal Form is an elusive concept. Strictly speaking any table
> conforms to 1NF if it has a candidate key and doesn't permit nulls. In
> practice we look out for attributes that contain more than one domain
> of values or have several columns representing the same attribute or
> contain data structures encoded in strings. Loosely speaking we say
> that these are a violation of 1NF. These are all subjective notions.
> Sometimes it seems "obvious" when the spirit of 1NF is being violated.
> Sometimes it is more tricky and the judgement will come down to one's
> knowledge and experience of what works and what doesn't.
> In your case we can say that using a pair of foreign keys in a single
> table to represent a many-to-many relationship is a proven design that
> appears frequently and that almost every database architect must have
> used it. There are alternatives but the major benefit of your design is
> that it's very easy to apply the necessary constraints for the business
> rules. Your table is in 1NF and I don't see a better design.
> --
> David Portas
> SQL Server MVP
> --
>

Normalization Question.

I have few doubts whether the following DB structure is good or rubbish.

Suppose you are an organization has many cinemas,Theatres,Venues etc:
Cinemas
Theatres
Venues
etc

and you want to store-retrieve about each one of this item

would you normalize like this

Organization Table
==================
orgID PK
OrgName

CinemaInOrganization
==================
OrgID PK
CinID PK

TheatreInOrganization
==================
OrgID PK
CinID PK

VenueInOrganization
====================
OrgID PK
VenID PK

Do you envisage any problem with querying tables with this structure?

Thanks a lot in advance

Not really, it largely depends on how your systems handle the Theatres, Cinemas and Venues.

Personally they are all types of venues, what commonality of data and function is shared across them all?

|||

Thanks a lot for your quick reply.
That was just a fictious example that strongly reflect a scenario I have to implement.

The commonality among them is that all these Items (Venues,Theatres etc) they all belong to an Organization.

What I wanted to establish is that if you have the following Tables:

Venue VeniD -VenName etc
Theatre ThID -THName etc
Cinema CinID -CinName

and then this organization has a one to many toabove tables should you create Link table for each one of them as I mentioned in the original post?

Thanks again

|||

My confusion.

I would only create link tables where you can have many to many relationships. It really complicates the issue to have those intermediate tables when you don't need them (in my view)

Normalization Question Regarding Column Combinations

We need to store land title information about properties in various
Australian states, but each state maintains it's own land title
registry and use different columns (well actually different
combinations of the same columns). For example:

Victoria store:

TorrensUnit
TorrensVolume
TorrensFolio

Queensland store:

TorrensCounty
TorrensLot
TorrensPlan
TorrensParish
TorrensUnit
TorrensVolume
TorrensTitleRef

There are 11 different columns and they are used in 8 different
combinations depending on the state.

Since we need to store information about land in different states I see
two possible solutions:

1. A sparse table containing the 11 columns with a CHECK constraint to
enforce the valid combinations.

2. A table for each state containing only the columns relevant to the
state with a foreign key relationship to the table containing the
common columns.

I'm not sure if the data type and length is consistent between states
yet (waiting to find this out) but assuming that it is which of these
approaches is going to be the most rigorous? I'm leaning towards (2)
but I don't like the feel of a table per state.>From a design standpoint I try to stay away from your first choice if
possible, seems like it'd be difficult to maintain. I like your second
point, but if there is a common set of data that is shared, then
"normalize" that information.

CREATE TABLE Store(
StoreID int IDENTITY(1,1) NOT NULL,
TorrensUnit varchar(50) NOT NULL,
TorrensVolume varchar(50) NOT NULL
)

CREATE TABLE StoreVictoria(
StoreVictoriaID int IDENTITY(1,1) NOT NULL,
StoreID INT NOT NULL, -- FK
--Specific columns
)

CREATE TABLE StoreQueensLand
(
StoreQueensLandID int IDENTITY(1,1) NOT NULL,
StoreID INT NOT NULL, -- FK
TorrensParish varchar(50),
TorrensTitleRef varchar(50)
--Specific columns
)|||On one hand, property does not move from state to state so one table
per state would work and make sense.

But do you ever view the set of all parcels of land in the country as
your unit of work? I woudl go with one table per state and VIEW that
has the global summary information.|||90% of the time we will be dealing with individual properties, there
will be time where we need to show a list of properties that are in
different states so a VIEW would make sense here. Thanks Celko, when's
your SQL Coding Standards book coming out? Looking forward to it.|||>> Thanks Celko, when's your SQL Coding Standards book coming out? Looking forward to it. <<

It has been out for a several weeks now.

http://www.amazon.com/exec/obidos/t...=glance&s=bookssql

normalization question

This question is inspired from my last question. It seems to me that for a
db to be properly normalized, should an area code be seperate from a phone
number? Is this incorrect?
SQL2K SP3
TIA, ChrisR
Probably not. The phone number really is (in North America) 10 digits, of
which the first three are the area code. Beyond N America, things change
somewhat (I believe).
That said, you can have a table of phone numbers where there is an area code
column and a number column, with a FK from the area code to a table of area
codes. This ensures that only known area codes are put into your phone
number table.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"ChrisR" <bla@.noemail.com> wrote in message
news:erXMq1M0EHA.2572@.tk2msftngp13.phx.gbl...
This question is inspired from my last question. It seems to me that for a
db to be properly normalized, should an area code be seperate from a phone
number? Is this incorrect?
SQL2K SP3
TIA, ChrisR
|||Actually, one could make an argument that both area code and prefix should
be separated out as keys, as they can be used to uniquely identify regions.
We use a database for that purpose, in order to determine qualification for
DSL. However, if you're only using them for display, they should definitely
be in the same column, IMO...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OxaITBN0EHA.2788@.TK2MSFTNGP15.phx.gbl...
> Probably not. The phone number really is (in North America) 10 digits, of
> which the first three are the area code. Beyond N America, things change
> somewhat (I believe).
> That said, you can have a table of phone numbers where there is an area
code
> column and a number column, with a FK from the area code to a table of
area
> codes. This ensures that only known area codes are put into your phone
> number table.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
|||I'd be careful about prefix. Where I work right now, I use 9+1+area
code+number. In another place, it was 4+area code+number. In yet another,
it was 8+area code+number. Of course, at home, it's 1+area code+number.
Sometimes, internationalization can be a right PITA...
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:urZFEGN0EHA.1332@.TK2MSFTNGP10.phx.gbl...
Actually, one could make an argument that both area code and prefix should
be separated out as keys, as they can be used to uniquely identify regions.
We use a database for that purpose, in order to determine qualification for
DSL. However, if you're only using them for display, they should definitely
be in the same column, IMO...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OxaITBN0EHA.2788@.TK2MSFTNGP15.phx.gbl...
> Probably not. The phone number really is (in North America) 10 digits, of
> which the first three are the area code. Beyond N America, things change
> somewhat (I believe).
> That said, you can have a table of phone numbers where there is an area
code
> column and a number column, with a FK from the area code to a table of
area
> codes. This ensures that only known area codes are put into your phone
> number table.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
|||>However, if you're only using them for display, they should definitely
> be in the same column, IMO...
Are you referring to the (ac and number), or the (ac and prefix) in this
statement?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:urZFEGN0EHA.1332@.TK2MSFTNGP10.phx.gbl...
> Actually, one could make an argument that both area code and prefix should
> be separated out as keys, as they can be used to uniquely identify
regions.
> We use a database for that purpose, in order to determine qualification
for
> DSL. However, if you're only using them for display, they should
definitely[vbcol=seagreen]
> be in the same column, IMO...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OxaITBN0EHA.2788@.TK2MSFTNGP15.phx.gbl...
of[vbcol=seagreen]
change
> code
> area
>
|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ejuU7JN0EHA.3452@.TK2MSFTNGP14.phx.gbl...
> I'd be careful about prefix. Where I work right now, I use 9+1+area
> code+number. In another place, it was 4+area code+number. In yet
another,
> it was 8+area code+number. Of course, at home, it's 1+area code+number.
> Sometimes, internationalization can be a right PITA...
>
I think we're talking about different things in regards to prefix. I'm
talking about:
(XXX) YYY - ZZZZ
Where XXX is the area code and YYY is the prefix. For
internationalization it's:
+CC - (AreaCode) - (Prefix) - (Something)
(CC = Country Code)
AFAIK, there is always some sort of areacode and prefix, although the
number of digits are variable, but the something can be totally different
depending on country -- there may be more levels of hierarchy, e.g. I think
in some Asian countries they have 4 or 5.
The local phone system you're on will determine the prefix you're
talking about, 9 + 1, 4 + , etc.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||"ChrisR" <bla@.noemail.com> wrote in message
news:e8Do%23JN0EHA.1296@.TK2MSFTNGP10.phx.gbl...
> Are you referring to the (ac and number), or the (ac and prefix) in this
> statement?
AC and number. I would store it (if it were for display only) as:
XXXYYYZZZZ, or maybe XXX-YYY-ZZZZ, or however you might want to display
it on the client.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||Ah. Where we live, what you call "prefix" we call "exchange". So, I guess
in order to validate things, there would only be certain exchanges within an
area code.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Bqf1TN0EHA.1524@.TK2MSFTNGP09.phx.gbl...
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ejuU7JN0EHA.3452@.TK2MSFTNGP14.phx.gbl...
> I'd be careful about prefix. Where I work right now, I use 9+1+area
> code+number. In another place, it was 4+area code+number. In yet
another,
> it was 8+area code+number. Of course, at home, it's 1+area code+number.
> Sometimes, internationalization can be a right PITA...
>
I think we're talking about different things in regards to prefix. I'm
talking about:
(XXX) YYY - ZZZZ
Where XXX is the area code and YYY is the prefix. For
internationalization it's:
+CC - (AreaCode) - (Prefix) - (Something)
(CC = Country Code)
AFAIK, there is always some sort of areacode and prefix, although the
number of digits are variable, but the something can be totally different
depending on country -- there may be more levels of hierarchy, e.g. I think
in some Asian countries they have 4 or 5.
The local phone system you're on will determine the prefix you're
talking about, 9 + 1, 4 + , etc.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||Just as an FYI:

> For
> internationalization it's:
> +CC - (AreaCode) - (Prefix) - (Something)
> (CC = Country Code)
> AFAIK, there is always some sort of areacode and prefix
In Sweden, we have no prefix. We have CountryCode (obviously), AreaCode and "something". :-)
Are code can be from two to four numbers. "Something" can be from five to 8 or nine numbers (not
sure how many it can go to).
As one point, Sweden was bragging, I believe it was in Newsweek, that they have the longest
telephone numbers in the world. In a country with some 8.5 (at the time) mill people. I found that
partly amusing, and partly really really worrying...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Bqf1TN0EHA.1524@.TK2MSFTNGP09.phx.gbl...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ejuU7JN0EHA.3452@.TK2MSFTNGP14.phx.gbl...
> another,
> I think we're talking about different things in regards to prefix. I'm
> talking about:
> (XXX) YYY - ZZZZ
> Where XXX is the area code and YYY is the prefix. For
> internationalization it's:
> +CC - (AreaCode) - (Prefix) - (Something)
> (CC = Country Code)
> AFAIK, there is always some sort of areacode and prefix, although the
> number of digits are variable, but the something can be totally different
> depending on country -- there may be more levels of hierarchy, e.g. I think
> in some Asian countries they have 4 or 5.
> The local phone system you're on will determine the prefix you're
> talking about, 9 + 1, 4 + , etc.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>
|||And for further info, we don't have any prefix or area code in Denmark. We
used to have a 2 digit area code in the past, but for many years we've just
had an 8 digit phonenumber.
In the past I worked on deploying a CRM system to our offices worldwide.
That was sometimes quite a challenge to get the phone- and fax numbers
entered in the right way into the system due to all the differencies in
phone number syntax around the world.
Regards
Steen
Tibor Karaszi wrote:[vbcol=seagreen]
> Just as an FYI:
>
> In Sweden, we have no prefix. We have CountryCode (obviously),
> AreaCode and "something". :-)
> Are code can be from two to four numbers. "Something" can be from
> five to 8 or nine numbers (not sure how many it can go to).
> As one point, Sweden was bragging, I believe it was in Newsweek, that
> they have the longest telephone numbers in the world. In a country
> with some 8.5 (at the time) mill people. I found that partly amusing,
> and partly really really worrying...
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
> message news:%23Bqf1TN0EHA.1524@.TK2MSFTNGP09.phx.gbl...

Normalization question

Hello,
I have a question about the benefits/gain or correctness
according to RDBMS convention of normalizing data tables.
My table in question is a single table that contains about
5,000,000 records that are queried to generate reports.
It does not feed any other tables. The un-normalized
table (tbl1) looks like this:
idNum Area Date Metric Value
1 A 1/1/2005 Q 3
1 A 1/1/2005 R 0
1 A 1/1/2005 S 17
1 A 1/1/2005 T 9
2 B 1/1/2005 Q 11
2 B 1/1/2005 R 8
2 B 1/1/2005 S 2
2 B 1/1/2005 T 30
...
10 A 1/2/2005 Q 3
10 A 1/2/2005 R 0
10 A 1/2/2005 S 17
10 A 1/2/2005 T 9
11 B 1/2/2005 Q 20
11 B 1/2/2005 R 22
11 B 1/2/2005 S 0
11 B 1/2/2005 T 17
...
So in this table I am capturing 4 metrics, Q, R, S, T for
each Area (A, B, ...) for each day. So for each Metric, I
am repeating the idNum, Area, and Date in each row.
In the normalized version, tbl1 would contain one
record/row for each idNum, Area, Date, and tbl2 would
contain the 4 rows of idNums, Metrics for each idNum,
Area, Date in tbl1
tbl1 tbl2
idNum Area Date idNum Metric Value
1 A 1/1/2005 1 Q 3
1 R 0
1 S 17
1 T 9
tbl1 would now shrink from 5,000,000 rows to about 50,000
(there's actually like 20 metrics for each area) but tbl2
would still have the 5,000,000 rows. My server has
300gigs drivespace, two 3gig cpu's, 8gigs ram. With the
normalized tables, I have 2 tables to manage now and have
to add a join for all the queries. I don't see the
benefit of normalizing my table for this scenario.
However, there may be concepts of RDBMS that I don't
understand where normalization is the correct way to go.
Any advice on this would be greatly appreciated.
Thanks,
Ron"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:1eec01c515d5$d62407b0$a601280a@.phx.gbl...
> idNum Area Date Metric Value
> 1 A 1/1/2005 Q 3
> 1 A 1/1/2005 R 0
> 1 A 1/1/2005 S 17
> 1 A 1/1/2005 T 9
> 2 B 1/1/2005 Q 11
> 2 B 1/1/2005 R 8
> 2 B 1/1/2005 S 2
> 2 B 1/1/2005 T 30
The main practical benefit of normalization (IMO) is elimination of
duplicate data. This has two sub-benefits: 1) reduction of storage space,
and 2) ease of updating. Depending on your circumstances, either or both of
these sub-benefits may not apply.|||Thanks for your reply. The main thing I was checking for
was if there were some concept I wasn't aware of. I can
see the reduced storage. And I suppose it is easier to
search through 50,000 rows vs 5,000,000 rows for stuff.
The conclusion I come to is that you can get more
performance with Normalization - or - it is easier to
write code for a single table but less performance. At my
place, the coding seems to change more than the data. I
guess the bottom line will be who is going to do more
work - me or the computer.

>--Original Message--
>"Ron" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1eec01c515d5$d62407b0$a601280a@.phx.gbl...
>The main practical benefit of normalization (IMO) is
elimination of
>duplicate data. This has two sub-benefits: 1) reduction
of storage space,
>and 2) ease of updating. Depending on your circumstances,
either or both of
>these sub-benefits may not apply.
>
>.
>|||Normalizing the table will prevent update anomalies and may also increase
performance, despite the joins. Querying a table with 50,000 records with a
n
index takes roughly 16 compares, whereas querying 5,000,000 records with an
index takes roughly 23 compares. In addition, smaller records mean more
records per data page, which should reduce disk reads. There is no doubt
that reading the entire table will perform slower with a join, but it has
been my experience that most queries retrieve only a small fraction of the
data, so an inner loop or merge join (with a clustered index on common) fiel
d
will actually perform better with two tables because of the reduced number o
f
disk reads required to retrieve the data.
I make it a habit to fully normalize a database and then denormalize only to
simplify joins of many tables, and then only when there is a perceived
performance problem that cannot be fixed with a judiciously placed index.
"Ron" wrote:

> Hello,
> I have a question about the benefits/gain or correctness
> according to RDBMS convention of normalizing data tables.
> My table in question is a single table that contains about
> 5,000,000 records that are queried to generate reports.
> It does not feed any other tables. The un-normalized
> table (tbl1) looks like this:
> idNum Area Date Metric Value
> 1 A 1/1/2005 Q 3
> 1 A 1/1/2005 R 0
> 1 A 1/1/2005 S 17
> 1 A 1/1/2005 T 9
> 2 B 1/1/2005 Q 11
> 2 B 1/1/2005 R 8
> 2 B 1/1/2005 S 2
> 2 B 1/1/2005 T 30
> ...
> 10 A 1/2/2005 Q 3
> 10 A 1/2/2005 R 0
> 10 A 1/2/2005 S 17
> 10 A 1/2/2005 T 9
> 11 B 1/2/2005 Q 20
> 11 B 1/2/2005 R 22
> 11 B 1/2/2005 S 0
> 11 B 1/2/2005 T 17
> ...
> So in this table I am capturing 4 metrics, Q, R, S, T for
> each Area (A, B, ...) for each day. So for each Metric, I
> am repeating the idNum, Area, and Date in each row.
> In the normalized version, tbl1 would contain one
> record/row for each idNum, Area, Date, and tbl2 would
> contain the 4 rows of idNums, Metrics for each idNum,
> Area, Date in tbl1
> tbl1 tbl2
> idNum Area Date idNum Metric Value
> 1 A 1/1/2005 1 Q 3
> 1 R 0
> 1 S 17
> 1 T 9
> tbl1 would now shrink from 5,000,000 rows to about 50,000
> (there's actually like 20 metrics for each area) but tbl2
> would still have the 5,000,000 rows. My server has
> 300gigs drivespace, two 3gig cpu's, 8gigs ram. With the
> normalized tables, I have 2 tables to manage now and have
> to add a join for all the queries. I don't see the
> benefit of normalizing my table for this scenario.
> However, there may be concepts of RDBMS that I don't
> understand where normalization is the correct way to go.
> Any advice on this would be greatly appreciated.
> Thanks,
> Ron
>|||>> So in this table I am capturing 4 metrics, Q, R, S, T for each Area
(A, B, ...) for each day. So for each Metric, I am repeating the
idNum, Area, and Date in each row. <<
1) id_num is redundant.
2) DATE is a reserved word.
3) Since the metrics are attribute of a reading, they need to be in
columns, not expressed as values. A normalized table would look like
this:
CREATE TABLE Readings
(area_code CHAR(1) NOT NULL,
collection_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
Q_value INTEGER DEFAULT 0 NOT NULL,
R_value INTEGER DEFAULT 0 NOT NULL,
S_value INTEGER DEFAULT 0 NOT NULL,
T_value INTEGER DEFAULT 0 NOT NULL,
PRIMARY KEY (area_code, collection_date ));
Obviously you will need to add CHECK() constraints and the right
DEFAULT values.

normalization question

This question is inspired from my last question. It seems to me that for a
db to be properly normalized, should an area code be seperate from a phone
number? Is this incorrect?
SQL2K SP3
TIA, ChrisRProbably not. The phone number really is (in North America) 10 digits, of
which the first three are the area code. Beyond N America, things change
somewhat (I believe).
That said, you can have a table of phone numbers where there is an area code
column and a number column, with a FK from the area code to a table of area
codes. This ensures that only known area codes are put into your phone
number table.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"ChrisR" <bla@.noemail.com> wrote in message
news:erXMq1M0EHA.2572@.tk2msftngp13.phx.gbl...
This question is inspired from my last question. It seems to me that for a
db to be properly normalized, should an area code be seperate from a phone
number? Is this incorrect?
SQL2K SP3
TIA, ChrisR|||Actually, one could make an argument that both area code and prefix should
be separated out as keys, as they can be used to uniquely identify regions.
We use a database for that purpose, in order to determine qualification for
DSL. However, if you're only using them for display, they should definitely
be in the same column, IMO...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OxaITBN0EHA.2788@.TK2MSFTNGP15.phx.gbl...
> Probably not. The phone number really is (in North America) 10 digits, of
> which the first three are the area code. Beyond N America, things change
> somewhat (I believe).
> That said, you can have a table of phone numbers where there is an area
code
> column and a number column, with a FK from the area code to a table of
area
> codes. This ensures that only known area codes are put into your phone
> number table.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>|||I'd be careful about prefix. Where I work right now, I use 9+1+area
code+number. In another place, it was 4+area code+number. In yet another,
it was 8+area code+number. Of course, at home, it's 1+area code+number.
Sometimes, internationalization can be a right PITA...
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:urZFEGN0EHA.1332@.TK2MSFTNGP10.phx.gbl...
Actually, one could make an argument that both area code and prefix should
be separated out as keys, as they can be used to uniquely identify regions.
We use a database for that purpose, in order to determine qualification for
DSL. However, if you're only using them for display, they should definitely
be in the same column, IMO...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OxaITBN0EHA.2788@.TK2MSFTNGP15.phx.gbl...
> Probably not. The phone number really is (in North America) 10 digits, of
> which the first three are the area code. Beyond N America, things change
> somewhat (I believe).
> That said, you can have a table of phone numbers where there is an area
code
> column and a number column, with a FK from the area code to a table of
area
> codes. This ensures that only known area codes are put into your phone
> number table.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>|||>However, if you're only using them for display, they should definitely
> be in the same column, IMO...
Are you referring to the (ac and number), or the (ac and prefix) in this
statement?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:urZFEGN0EHA.1332@.TK2MSFTNGP10.phx.gbl...
> Actually, one could make an argument that both area code and prefix should
> be separated out as keys, as they can be used to uniquely identify
regions.
> We use a database for that purpose, in order to determine qualification
for
> DSL. However, if you're only using them for display, they should
definitely
> be in the same column, IMO...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OxaITBN0EHA.2788@.TK2MSFTNGP15.phx.gbl...
of[vbcol=seagreen]
change[vbcol=seagreen]
> code
> area
>|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ejuU7JN0EHA.3452@.TK2MSFTNGP14.phx.gbl...
> I'd be careful about prefix. Where I work right now, I use 9+1+area
> code+number. In another place, it was 4+area code+number. In yet
another,
> it was 8+area code+number. Of course, at home, it's 1+area code+number.
> Sometimes, internationalization can be a right PITA...
>
I think we're talking about different things in regards to prefix. I'm
talking about :
(XXX) YYY - ZZZZ
Where XXX is the area code and YYY is the prefix. For
internationalization it's:
+CC - (AreaCode) - (Prefix) - (Something)
(CC = Country Code)
AFAIK, there is always some sort of areacode and prefix, although the
number of digits are variable, but the something can be totally different
depending on country -- there may be more levels of hierarchy, e.g. I think
in some Asian countries they have 4 or 5.
The local phone system you're on will determine the prefix you're
talking about, 9 + 1, 4 + , etc.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||"ChrisR" <bla@.noemail.com> wrote in message
news:e8Do%23JN0EHA.1296@.TK2MSFTNGP10.phx.gbl...
> Are you referring to the (ac and number), or the (ac and prefix) in this
> statement?
AC and number. I would store it (if it were for display only) as:
XXXYYYZZZZ, or maybe XXX-YYY-ZZZZ, or however you might want to display
it on the client.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Ah. Where we live, what you call "prefix" we call "exchange". So, I guess
in order to validate things, there would only be certain exchanges within an
area code.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Bqf1TN0EHA.1524@.TK2MSFTNGP09.phx.gbl...
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ejuU7JN0EHA.3452@.TK2MSFTNGP14.phx.gbl...
> I'd be careful about prefix. Where I work right now, I use 9+1+area
> code+number. In another place, it was 4+area code+number. In yet
another,
> it was 8+area code+number. Of course, at home, it's 1+area code+number.
> Sometimes, internationalization can be a right PITA...
>
I think we're talking about different things in regards to prefix. I'm
talking about :
(XXX) YYY - ZZZZ
Where XXX is the area code and YYY is the prefix. For
internationalization it's:
+CC - (AreaCode) - (Prefix) - (Something)
(CC = Country Code)
AFAIK, there is always some sort of areacode and prefix, although the
number of digits are variable, but the something can be totally different
depending on country -- there may be more levels of hierarchy, e.g. I think
in some Asian countries they have 4 or 5.
The local phone system you're on will determine the prefix you're
talking about, 9 + 1, 4 + , etc.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Just as an FYI:

> For
> internationalization it's:
> +CC - (AreaCode) - (Prefix) - (Something)
> (CC = Country Code)
> AFAIK, there is always some sort of areacode and prefix
In Sweden, we have no prefix. We have CountryCode (obviously), AreaCode and
"something". :-)
Are code can be from two to four numbers. "Something" can be from five to 8
or nine numbers (not
sure how many it can go to).
As one point, Sweden was bragging, I believe it was in Newsweek, that they h
ave the longest
telephone numbers in the world. In a country with some 8.5 (at the time) mil
l people. I found that
partly amusing, and partly really really worrying...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Bqf1TN0EHA.1524@.TK2MSFTNGP09.phx.gbl...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ejuU7JN0EHA.3452@.TK2MSFTNGP14.phx.gbl...
> another,
> I think we're talking about different things in regards to prefix. I'
m
> talking about :
> (XXX) YYY - ZZZZ
> Where XXX is the area code and YYY is the prefix. For
> internationalization it's:
> +CC - (AreaCode) - (Prefix) - (Something)
> (CC = Country Code)
> AFAIK, there is always some sort of areacode and prefix, although the
> number of digits are variable, but the something can be totally different
> depending on country -- there may be more levels of hierarchy, e.g. I thin
k
> in some Asian countries they have 4 or 5.
> The local phone system you're on will determine the prefix you're
> talking about, 9 + 1, 4 + , etc.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>|||And for further info, we don't have any prefix or area code in Denmark. We
used to have a 2 digit area code in the past, but for many years we've just
had an 8 digit phonenumber.
In the past I worked on deploying a CRM system to our offices worldwide.
That was sometimes quite a challenge to get the phone- and fax numbers
entered in the right way into the system due to all the differencies in
phone number syntax around the world.
Regards
Steen
Tibor Karaszi wrote:[vbcol=seagreen]
> Just as an FYI:
>
> In Sweden, we have no prefix. We have CountryCode (obviously),
> AreaCode and "something". :-)
> Are code can be from two to four numbers. "Something" can be from
> five to 8 or nine numbers (not sure how many it can go to).
> As one point, Sweden was bragging, I believe it was in Newsweek, that
> they have the longest telephone numbers in the world. In a country
> with some 8.5 (at the time) mill people. I found that partly amusing,
> and partly really really worrying...
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
> message news:%23Bqf1TN0EHA.1524@.TK2MSFTNGP09.phx.gbl...

Normalization question

Hello,

I'm building an OLTP employee roster application, and I have the data model normalized, but I'm finding it challenging to update the data in all of the underlying tables via a web form and stored procedures. I'm getting the direction to keep things normalized, but I'm struggling with table updates.

Is it sound OLTP database practice to write records to a "roster_data" table that is a foreign key repository for all of the underlying tables (almost like a view without the update restrictions)? It would be much easier to manage this table of foreign keys (fact table) instead of writing several stored procedures to keep all of the related tables (dimensions) updated. (almost like a star schema in OLAP)

I know I'm being vague, but I would like to bounce off some OLTP experts.

Thanks,

Jim

What type of data is being changed?

Is it key data?

Would CASCADE UPDATE work?

More information, table DDL, etc., would be useful.

|||

Not that you are a beginner in normalization topic, but the following links should get you the idea in thsi regard:

http://www.informit.com/articles/article.asp?p=27785&rl=1 & http://www.sqlmag.com/Articles/Index.cfm?ArticleID=4887

|||

Everyone,

My basic issue was keeping my underlying normalized data model updated via one VIEW, and I was running into the "can't updated more than one base table issue." After some research, I found the INSTEAD OF triggers -- this functionality will enable me to update all of my underyling tables using one VIEW.

Thanks everyone and have a great holiday!

Jim

Normalization question

I was asked by a client how I would normalize some data that they had. An
example follows:
XIBMH002602382,XIBMH005,D:\005\00002382.TIF,
XIBMH002602383,XIBMH005,D:\005\00002383.TIF,
XIBMH002602384,XIBMH005,D:\005\00002384.TIF,
XIBMH002602385,XIBMH005,D:\005\00002385.TIF,
XIBMH002700001,XIBMH005,D:\005\00002386.TIF,Y
XIBMH002700002,XIBMH005,D:\005\00002387.TIF,
XIBMH002700003,XIBMH005,D:\005\00002388.TIF,
XIBMH002700004,XIBMH005,D:\005\00002389.TIF,
XIBMH002700005,XIBMH005,D:\005\00002390.TIF,
XIBMH002700006,XIBMH005,D:\005\00002391.TIF,
XIBMH002700007,XIBMH005,D:\005\00002392.TIF,
Where:
a. the first piece of data (e.g. XIBMH002602382) is the control
number identifying a scanned image.
b. the second piece of data (e.g. XIBMH005) is the volume name
for the CD the images are stored on.
c. the third piece of data (e.g. D:\005\00002382.TIF) is the
fully qualified filename of the image file.
d. the fourth piece of data (which is Y if it is present) says
whether this image is the first page of a multiple-page document.
Would you put the Volume name in another table with a keyed field and maybe
the Drive and Folder into another, or should the data be kept into one
table?
Just asking for your thoughts on what you would do with this data
ThanksI assume that the table is modeling a filename. The only concern I see is
if the directory name is functionally dependent on the volume name , it
would be better to have two tables:
file
===
control_number (pk)
--
volume (foreign key to volume table)
filename
first_page_of_multipage
volume
=====
volume (pk)
--
root directory
What isn't clear is the multipage thing:

> XIBMH002700001,XIBMH005,D:\005\00002386.TIF,Y
> XIBMH002700002,XIBMH005,D:\005\00002387.TIF,
>
Dos this mean there are multiple pages stored in the tif? If so, a better
way to do this would be to have an attribute of number_of_pages, but the yes
or no thing will work. If the pages are different files, then I would think
storing these filenames in the table would be better.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Gary Paris" <garyparis@.yada.com> wrote in message
news:%23qmK7zlCFHA.4072@.TK2MSFTNGP10.phx.gbl...
>I was asked by a client how I would normalize some data that they had. An
>example follows:
> XIBMH002602382,XIBMH005,D:\005\00002382.TIF,
> XIBMH002602383,XIBMH005,D:\005\00002383.TIF,
> XIBMH002602384,XIBMH005,D:\005\00002384.TIF,
> XIBMH002602385,XIBMH005,D:\005\00002385.TIF,
> XIBMH002700001,XIBMH005,D:\005\00002386.TIF,Y
> XIBMH002700002,XIBMH005,D:\005\00002387.TIF,
> XIBMH002700003,XIBMH005,D:\005\00002388.TIF,
> XIBMH002700004,XIBMH005,D:\005\00002389.TIF,
> XIBMH002700005,XIBMH005,D:\005\00002390.TIF,
> XIBMH002700006,XIBMH005,D:\005\00002391.TIF,
> XIBMH002700007,XIBMH005,D:\005\00002392.TIF,
>
>
> Where:
> a. the first piece of data (e.g. XIBMH002602382) is the
> control number identifying a scanned image.
> b. the second piece of data (e.g. XIBMH005) is the volume name
> for the CD the images are stored on.
> c. the third piece of data (e.g. D:\005\00002382.TIF) is the
> fully qualified filename of the image file.
> d. the fourth piece of data (which is Y if it is present) says
> whether this image is the first page of a multiple-page document.
>
>
> Would you put the Volume name in another table with a keyed field and
> maybe the Drive and Folder into another, or should the data be kept into
> one table?
>
> Just asking for your thoughts on what you would do with this data
>
> Thanks
>
>|||>> I was asked by a client how I would normalize some data that they had.
If this data is in a table with three columns, I would suggest you ask your
client why he considers this data not normalized. You should have the clue
right then.
It all depends on how the business model distingushes one set of attributes
as a single fact from another. Do you have a need to update/insert/delete a
volume name without affecting the drive and folder information or vice
versa? Based on the consideration that a volume name determines the
directory, Louis' suggestion is a reasonable one.
Anithsql

normalization question

This question is inspired from my last question. It seems to me that for a
db to be properly normalized, should an area code be seperate from a phone
number? Is this incorrect?
--
SQL2K SP3
TIA, ChrisRProbably not. The phone number really is (in North America) 10 digits, of
which the first three are the area code. Beyond N America, things change
somewhat (I believe).
That said, you can have a table of phone numbers where there is an area code
column and a number column, with a FK from the area code to a table of area
codes. This ensures that only known area codes are put into your phone
number table.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"ChrisR" <bla@.noemail.com> wrote in message
news:erXMq1M0EHA.2572@.tk2msftngp13.phx.gbl...
This question is inspired from my last question. It seems to me that for a
db to be properly normalized, should an area code be seperate from a phone
number? Is this incorrect?
--
SQL2K SP3
TIA, ChrisR|||Actually, one could make an argument that both area code and prefix should
be separated out as keys, as they can be used to uniquely identify regions.
We use a database for that purpose, in order to determine qualification for
DSL. However, if you're only using them for display, they should definitely
be in the same column, IMO...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OxaITBN0EHA.2788@.TK2MSFTNGP15.phx.gbl...
> Probably not. The phone number really is (in North America) 10 digits, of
> which the first three are the area code. Beyond N America, things change
> somewhat (I believe).
> That said, you can have a table of phone numbers where there is an area
code
> column and a number column, with a FK from the area code to a table of
area
> codes. This ensures that only known area codes are put into your phone
> number table.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>|||I'd be careful about prefix. Where I work right now, I use 9+1+area
code+number. In another place, it was 4+area code+number. In yet another,
it was 8+area code+number. Of course, at home, it's 1+area code+number.
Sometimes, internationalization can be a right PITA...
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:urZFEGN0EHA.1332@.TK2MSFTNGP10.phx.gbl...
Actually, one could make an argument that both area code and prefix should
be separated out as keys, as they can be used to uniquely identify regions.
We use a database for that purpose, in order to determine qualification for
DSL. However, if you're only using them for display, they should definitely
be in the same column, IMO...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OxaITBN0EHA.2788@.TK2MSFTNGP15.phx.gbl...
> Probably not. The phone number really is (in North America) 10 digits, of
> which the first three are the area code. Beyond N America, things change
> somewhat (I believe).
> That said, you can have a table of phone numbers where there is an area
code
> column and a number column, with a FK from the area code to a table of
area
> codes. This ensures that only known area codes are put into your phone
> number table.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>|||>However, if you're only using them for display, they should definitely
> be in the same column, IMO...
Are you referring to the (ac and number), or the (ac and prefix) in this
statement?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:urZFEGN0EHA.1332@.TK2MSFTNGP10.phx.gbl...
> Actually, one could make an argument that both area code and prefix should
> be separated out as keys, as they can be used to uniquely identify
regions.
> We use a database for that purpose, in order to determine qualification
for
> DSL. However, if you're only using them for display, they should
definitely
> be in the same column, IMO...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OxaITBN0EHA.2788@.TK2MSFTNGP15.phx.gbl...
> > Probably not. The phone number really is (in North America) 10 digits,
of
> > which the first three are the area code. Beyond N America, things
change
> > somewhat (I believe).
> >
> > That said, you can have a table of phone numbers where there is an area
> code
> > column and a number column, with a FK from the area code to a table of
> area
> > codes. This ensures that only known area codes are put into your phone
> > number table.
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com
> >
>|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ejuU7JN0EHA.3452@.TK2MSFTNGP14.phx.gbl...
> I'd be careful about prefix. Where I work right now, I use 9+1+area
> code+number. In another place, it was 4+area code+number. In yet
another,
> it was 8+area code+number. Of course, at home, it's 1+area code+number.
> Sometimes, internationalization can be a right PITA...
>
I think we're talking about different things in regards to prefix. I'm
talking about:
(XXX) YYY - ZZZZ
Where XXX is the area code and YYY is the prefix. For
internationalization it's:
+CC - (AreaCode) - (Prefix) - (Something)
(CC = Country Code)
AFAIK, there is always some sort of areacode and prefix, although the
number of digits are variable, but the something can be totally different
depending on country -- there may be more levels of hierarchy, e.g. I think
in some Asian countries they have 4 or 5.
The local phone system you're on will determine the prefix you're
talking about, 9 + 1, 4 + , etc.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||"ChrisR" <bla@.noemail.com> wrote in message
news:e8Do%23JN0EHA.1296@.TK2MSFTNGP10.phx.gbl...
> >However, if you're only using them for display, they should definitely
> > be in the same column, IMO...
> Are you referring to the (ac and number), or the (ac and prefix) in this
> statement?
AC and number. I would store it (if it were for display only) as:
XXXYYYZZZZ, or maybe XXX-YYY-ZZZZ, or however you might want to display
it on the client.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Ah. Where we live, what you call "prefix" we call "exchange". So, I guess
in order to validate things, there would only be certain exchanges within an
area code.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Bqf1TN0EHA.1524@.TK2MSFTNGP09.phx.gbl...
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ejuU7JN0EHA.3452@.TK2MSFTNGP14.phx.gbl...
> I'd be careful about prefix. Where I work right now, I use 9+1+area
> code+number. In another place, it was 4+area code+number. In yet
another,
> it was 8+area code+number. Of course, at home, it's 1+area code+number.
> Sometimes, internationalization can be a right PITA...
>
I think we're talking about different things in regards to prefix. I'm
talking about:
(XXX) YYY - ZZZZ
Where XXX is the area code and YYY is the prefix. For
internationalization it's:
+CC - (AreaCode) - (Prefix) - (Something)
(CC = Country Code)
AFAIK, there is always some sort of areacode and prefix, although the
number of digits are variable, but the something can be totally different
depending on country -- there may be more levels of hierarchy, e.g. I think
in some Asian countries they have 4 or 5.
The local phone system you're on will determine the prefix you're
talking about, 9 + 1, 4 + , etc.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Just as an FYI:
> For
> internationalization it's:
> +CC - (AreaCode) - (Prefix) - (Something)
> (CC = Country Code)
> AFAIK, there is always some sort of areacode and prefix
In Sweden, we have no prefix. We have CountryCode (obviously), AreaCode and "something". :-)
Are code can be from two to four numbers. "Something" can be from five to 8 or nine numbers (not
sure how many it can go to).
As one point, Sweden was bragging, I believe it was in Newsweek, that they have the longest
telephone numbers in the world. In a country with some 8.5 (at the time) mill people. I found that
partly amusing, and partly really really worrying...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Bqf1TN0EHA.1524@.TK2MSFTNGP09.phx.gbl...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ejuU7JN0EHA.3452@.TK2MSFTNGP14.phx.gbl...
> > I'd be careful about prefix. Where I work right now, I use 9+1+area
> > code+number. In another place, it was 4+area code+number. In yet
> another,
> > it was 8+area code+number. Of course, at home, it's 1+area code+number.
> >
> > Sometimes, internationalization can be a right PITA...
> >
> I think we're talking about different things in regards to prefix. I'm
> talking about:
> (XXX) YYY - ZZZZ
> Where XXX is the area code and YYY is the prefix. For
> internationalization it's:
> +CC - (AreaCode) - (Prefix) - (Something)
> (CC = Country Code)
> AFAIK, there is always some sort of areacode and prefix, although the
> number of digits are variable, but the something can be totally different
> depending on country -- there may be more levels of hierarchy, e.g. I think
> in some Asian countries they have 4 or 5.
> The local phone system you're on will determine the prefix you're
> talking about, 9 + 1, 4 + , etc.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>|||And for further info, we don't have any prefix or area code in Denmark. We
used to have a 2 digit area code in the past, but for many years we've just
had an 8 digit phonenumber.
In the past I worked on deploying a CRM system to our offices worldwide.
That was sometimes quite a challenge to get the phone- and fax numbers
entered in the right way into the system due to all the differencies in
phone number syntax around the world.
Regards
Steen
Tibor Karaszi wrote:
> Just as an FYI:
>> For
>> internationalization it's:
>> +CC - (AreaCode) - (Prefix) - (Something)
>> (CC = Country Code)
>> AFAIK, there is always some sort of areacode and prefix
> In Sweden, we have no prefix. We have CountryCode (obviously),
> AreaCode and "something". :-)
> Are code can be from two to four numbers. "Something" can be from
> five to 8 or nine numbers (not sure how many it can go to).
> As one point, Sweden was bragging, I believe it was in Newsweek, that
> they have the longest telephone numbers in the world. In a country
> with some 8.5 (at the time) mill people. I found that partly amusing,
> and partly really really worrying...
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
> message news:%23Bqf1TN0EHA.1524@.TK2MSFTNGP09.phx.gbl...
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:ejuU7JN0EHA.3452@.TK2MSFTNGP14.phx.gbl...
>> I'd be careful about prefix. Where I work right now, I use 9+1+area
>> code+number. In another place, it was 4+area code+number. In yet
>> another,
>> it was 8+area code+number. Of course, at home, it's 1+area
>> code+number.
>> Sometimes, internationalization can be a right PITA...
>>
>> I think we're talking about different things in regards to
>> prefix. I'm talking about:
>> (XXX) YYY - ZZZZ
>> Where XXX is the area code and YYY is the prefix. For
>> internationalization it's:
>> +CC - (AreaCode) - (Prefix) - (Something)
>> (CC = Country Code)
>> AFAIK, there is always some sort of areacode and prefix,
>> although the number of digits are variable, but the something can be
>> totally different depending on country -- there may be more levels
>> of hierarchy, e.g. I think in some Asian countries they have 4 or 5.
>> The local phone system you're on will determine the prefix you're
>> talking about, 9 + 1, 4 + , etc.
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.sqljunkies.com/weblog/amachanic
>> --|||"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:elA9%239V0EHA.3408@.tk2msftngp13.phx.gbl...
> And for further info, we don't have any prefix or area code in Denmark. We
> used to have a 2 digit area code in the past, but for many years we've
just
> had an 8 digit phonenumber.
> >
> > In Sweden, we have no prefix. We have CountryCode (obviously),
> > AreaCode and "something". :-)
Thanks for the info guys!
Both are countries I haven't worked with yet. I had a bear of a time w/
Asia (mostly due to lack of quality data, but it's been a few years since
that project so maybe things have improved) -- I can't wait until I get to
cover Europe :-)
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--