I have a lookup table called States, I have multiple other tables that use this lookup table, but I can only relate to one other table. My question is if I change a state name how do I enforce that change to the other tables that CANT be related? I know it is a design flaw and need some kind of joining table. I am having difficulty understanding and incorporating first normal form in my database. Here is what i have so far.
Example:
Table 1
========|============|=============|========|
CustomerID BillingAddressID ShippingAddressID OtherFields
========|============|=============|========|
Table 2
===========|======|========|
BillingAddressID StateID OtherFields
===========|======|========|
Table 3
=============|======|========|
ShippingAddressID StateID OtherFields
=============|======|========|
Table 4
======|====|
StateID Name
======|====|
How do I relate table 4 with table 2 & 3 for referential integrity? Or create a joiner table?You have it already...you just need to enforce the relationship with a FOREIGN KEY CONSTRAINT
And please don't tell me that StateID is an entity column...|||I can only relate table 4 with either table 2 or 3 but not both. what do you mean by entity table? Excuse my ignorance.
Thanks|||I'm not sure I follow you...
USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE Table4 (
StateID int PRIMARY KEY
, [Name] varchar(50)
)
CREATE TABLE Table2 (
BillingAddressID int PRIMARY KEY
, StateID int
, OtherFields int
, CONSTRAINT FK_Table2 FOREIGN KEY (StateID) REFERENCES Table4(StateId)
)
CREATE TABLE Table3 (
ShippingAddressID int PRIMARY KEY
, StateID int
, OtherFields int
, CONSTRAINT FK_Table3 FOREIGN KEY (StateID) REFERENCES Table4(StateId)
)
GO
INSERT INTO Table3(ShippingAddressID, StateID) SELECT 1,1
INSERT INTO Table2(BillingAddressID, StateID) SELECT 1,1
GO
INSERT INTO Table4(StateID, [Name]) SELECT 1, 'New Jersey'
GO
INSERT INTO Table3(ShippingAddressID, StateID, OtherFields) SELECT 1,1, 1
INSERT INTO Table2(BillingAddressID , StateID, OtherFields) SELECT 1,1, 1
GO
SELECT 'Table3' AS Source, l.ShippingAddressID, l.StateID, l.OtherFields, r.[Name]
FROM Table3 l
JOIN Table4 r
ON l.StateID = r.StateID
UNION ALL
SELECT 'Table2' AS Source, l.BillingAddressID, l.StateID, l.OtherFields, r.[Name]
FROM Table2 l
JOIN Table4 r
ON l.StateID = r.StateID
GO
SET NOCOUNT OFF
DROP TABLE Table2
DROP TABLE Table3
DROP TABLE Table4
GO|||Oh, I meant IDENTITY Column by the way...not Entity...sorry|||why wouldn't StateID be an identity column?
are you perhaps suggesting that a natural key like NJ would be more suitable?
better hide, because all the surrogate key fanatics are gonna come gunnin' for ya...|||Hey...it's their perogative...let them join till the cows come home...
And there are times for it...but not as many as it's abused...
Here's a question. Would you use an identity column for a phone number? SSN? Stock Market symbol?|||Would you use an identity column for a phone number? SSN? Stock Market symbol?me? never
you must have me confused with those surrogate key fanatics, who blindly require an identity column in every table...
i know a guy on, ahem, another forum web site, who once took a poll on the subject
see Surrogate Keys.The Devils spawn (OK Not really) (http://weblogs.sqlteam.com/brettk/archive/2004/06/09/1530.aspx)
Showing posts with label states. Show all posts
Showing posts with label states. Show all posts
Wednesday, March 21, 2012
Referential Integrity - Countries & States/Provinces
I have a Country table with (surprise!) countries, and a Locale table
with states, territories, provinces, etc. I also have another table,
Port, that usually contains Country information but only occasionally
contains Locale information.
My question is, how do I set up the foreign keys maintain referential
integrity for Locale data but still allow for NULLs?
The following seems to fail whenever I have NULLs for the Country
and/or Locale of a Port:
CREATE TABLE Country
(
CountryID nchar(2) NOT NULL PRIMARY KEY,
Country nvarchar(50) NOT NULL
)
CREATE TABLE Locale
(
CountryID nchar(2) NOT NULL REFERENCES Country (CountryID),
LocaleID nvarchar(3) NOT NULL,
Locale nvarchar(100) NOT NULL,
PRIMARY KEY (CountryID, LocaleID)
)
CREATE TABLE Port
(
PortID smallint NOT NULL PRIMARY KEY,
Port nvarchar(100) NOT NULL,
SeaID tinyint NOT NULL REFERENCES Sea (SeaID),
CountryID nchar(2) NULL, --sometimes NULL!!!
LocaleID nvarchar(3) NULL, --often NULL!!!
FOREIGN KEY (CountryID, LocaleID) REFERENCES Locale (CountryID,
LocaleID)
)
Thanks!
-DanLook up the nested sets model and buidl a hierarchy of geography.
Also, ISO country codes are CHAR(3). The trick will be in having a
node type in the locations table and so constraints on the leaf nodes.|||"Dan Manes" <danthman@.cox.net> wrote in message
news:1148083925.869411.11720@.y43g2000cwc.googlegroups.com...
>I have a Country table with (surprise!) countries, and a Locale table
> with states, territories, provinces, etc. I also have another table,
> Port, that usually contains Country information but only occasionally
> contains Locale information.
> My question is, how do I set up the foreign keys maintain referential
> integrity for Locale data but still allow for NULLs?
> The following seems to fail whenever I have NULLs for the Country
> and/or Locale of a Port:
> CREATE TABLE Country
> (
> CountryID nchar(2) NOT NULL PRIMARY KEY,
> Country nvarchar(50) NOT NULL
> )
> CREATE TABLE Locale
> (
> CountryID nchar(2) NOT NULL REFERENCES Country (CountryID),
> LocaleID nvarchar(3) NOT NULL,
> Locale nvarchar(100) NOT NULL,
> PRIMARY KEY (CountryID, LocaleID)
> )
> CREATE TABLE Port
> (
> PortID smallint NOT NULL PRIMARY KEY,
> Port nvarchar(100) NOT NULL,
> SeaID tinyint NOT NULL REFERENCES Sea (SeaID),
> CountryID nchar(2) NULL, --sometimes NULL!!!
> LocaleID nvarchar(3) NULL, --often NULL!!!
> FOREIGN KEY (CountryID, LocaleID) REFERENCES Locale (CountryID,
> LocaleID)
> )
>
Doesn't fail for me:
CREATE TABLE Country
(
CountryID nchar(2) NOT NULL PRIMARY KEY,
Country nvarchar(50) NOT NULL
)
CREATE TABLE Locale
(
CountryID nchar(2) NOT NULL REFERENCES Country (CountryID),
LocaleID nvarchar(3) NOT NULL,
Locale nvarchar(100) NOT NULL,
PRIMARY KEY (CountryID, LocaleID)
)
CREATE TABLE Port
(
PortID smallint NOT NULL PRIMARY KEY,
Port nvarchar(100) NOT NULL,
SeaID tinyint NOT NULL, -- REFERENCES Sea (SeaID),
CountryID nchar(2) NULL, --sometimes NULL!!!
LocaleID nvarchar(3) NULL, --often NULL!!!
FOREIGN KEY (CountryID, LocaleID) REFERENCES Locale (CountryID,
LocaleID)
)
go
insert into country values (1,'usa')
insert into locale values (1,'tx','Texas')
insert into port values (1,'Port Arthur',0,1,'tx')
insert into port values (2,'Houston',0,1,null)
insert into port values (3,'Dallas',0,null,null)
David|||Thanks, David. You're right. My problem was that what I thought were
NULLs in my source data table turned out to be blanks--not the same
thing. Once I replaced them with NULLs, everything was fine.
As for CELKO's comment about ISO Country codes being CHAR(3), there's
both a 2- and 3-character ISO standard. My example just happened to
have the 2-character version.
-Dan|||Go with the 3; the 2 is about to be deprecated becuase we just have too
many countries declaring themselves.|||If only it were that simple :) My real Country table actually has five
different code columns (including 2- *and* 3-digit ISO) because users
will be getting their country code data from several different
sources/databases, all of which use their own codes. Of course, some of
these source databases have country codes for entities the ISO doesn't
even consider countries.
The result, a big messy normalization problem. Don't you just love when
people ignore standards?
Anyway, even though it seems unlikely we'll go from 243 to 676
countries anytime soon, the 3-digit codes also have the advantage of
being more human readable. E.g., with two characters, Canada = CA and
China = CN; with three, Canada = CAN and China = CHN.
-Dan|||> Go with the 3; the 2 is about to be deprecated becuase we just have too
> many countries declaring themselves.
Looks pretty current to me:
http://www.iso.org/iso/en/prods-ser...n1.
html
Can you post your source that states the 2 code is going to be depreciated?
It will cause absolute confusion over here because travelling on the content
you need to have a sticker with your country code on your car and to my
knowledge Great Britian is still GB.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1148150829.923946.28160@.u72g2000cwu.googlegroups.com...
> Go with the 3; the 2 is about to be deprecated becuase we just have too
> many countries declaring themselves.
>|||Some stickers are 1,2 or 3 characters
I = Italy
F = France
NL = the Netherlands
SLO =Slovenia
I don't think those follow any ISO standards
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Makes matters worse, I wonder what standard us Europeans use then for that?
The only useful thing I found was from the US CIA site:
http://www.cia.gov/cia/publications...appendix-d.html and
there is no mention of 2 code being depreciated, to make matters even more
interesting ISO 3166 has 3 versions, 2 letter, 3 letter and 3 digit!
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"SQL Menace" <denis.gobo@.gmail.com> wrote in message
news:1148222151.073946.222330@.i40g2000cwc.googlegroups.com...
> Some stickers are 1,2 or 3 characters
> I = Italy
> F = France
> NL = the Netherlands
> SLO =Slovenia
> I don't think those follow any ISO standards
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||On Sun, 21 May 2006 17:48:38 +0100, Tony Rogerson wrote:
> to make matters even more
>interesting ISO 3166 has 3 versions, 2 letter, 3 letter and 3 digit!
"The nice thing about standards is that there are so many of them"
-- Unknown
Hugo Kornelis, SQL Server MVP
with states, territories, provinces, etc. I also have another table,
Port, that usually contains Country information but only occasionally
contains Locale information.
My question is, how do I set up the foreign keys maintain referential
integrity for Locale data but still allow for NULLs?
The following seems to fail whenever I have NULLs for the Country
and/or Locale of a Port:
CREATE TABLE Country
(
CountryID nchar(2) NOT NULL PRIMARY KEY,
Country nvarchar(50) NOT NULL
)
CREATE TABLE Locale
(
CountryID nchar(2) NOT NULL REFERENCES Country (CountryID),
LocaleID nvarchar(3) NOT NULL,
Locale nvarchar(100) NOT NULL,
PRIMARY KEY (CountryID, LocaleID)
)
CREATE TABLE Port
(
PortID smallint NOT NULL PRIMARY KEY,
Port nvarchar(100) NOT NULL,
SeaID tinyint NOT NULL REFERENCES Sea (SeaID),
CountryID nchar(2) NULL, --sometimes NULL!!!
LocaleID nvarchar(3) NULL, --often NULL!!!
FOREIGN KEY (CountryID, LocaleID) REFERENCES Locale (CountryID,
LocaleID)
)
Thanks!
-DanLook up the nested sets model and buidl a hierarchy of geography.
Also, ISO country codes are CHAR(3). The trick will be in having a
node type in the locations table and so constraints on the leaf nodes.|||"Dan Manes" <danthman@.cox.net> wrote in message
news:1148083925.869411.11720@.y43g2000cwc.googlegroups.com...
>I have a Country table with (surprise!) countries, and a Locale table
> with states, territories, provinces, etc. I also have another table,
> Port, that usually contains Country information but only occasionally
> contains Locale information.
> My question is, how do I set up the foreign keys maintain referential
> integrity for Locale data but still allow for NULLs?
> The following seems to fail whenever I have NULLs for the Country
> and/or Locale of a Port:
> CREATE TABLE Country
> (
> CountryID nchar(2) NOT NULL PRIMARY KEY,
> Country nvarchar(50) NOT NULL
> )
> CREATE TABLE Locale
> (
> CountryID nchar(2) NOT NULL REFERENCES Country (CountryID),
> LocaleID nvarchar(3) NOT NULL,
> Locale nvarchar(100) NOT NULL,
> PRIMARY KEY (CountryID, LocaleID)
> )
> CREATE TABLE Port
> (
> PortID smallint NOT NULL PRIMARY KEY,
> Port nvarchar(100) NOT NULL,
> SeaID tinyint NOT NULL REFERENCES Sea (SeaID),
> CountryID nchar(2) NULL, --sometimes NULL!!!
> LocaleID nvarchar(3) NULL, --often NULL!!!
> FOREIGN KEY (CountryID, LocaleID) REFERENCES Locale (CountryID,
> LocaleID)
> )
>
Doesn't fail for me:
CREATE TABLE Country
(
CountryID nchar(2) NOT NULL PRIMARY KEY,
Country nvarchar(50) NOT NULL
)
CREATE TABLE Locale
(
CountryID nchar(2) NOT NULL REFERENCES Country (CountryID),
LocaleID nvarchar(3) NOT NULL,
Locale nvarchar(100) NOT NULL,
PRIMARY KEY (CountryID, LocaleID)
)
CREATE TABLE Port
(
PortID smallint NOT NULL PRIMARY KEY,
Port nvarchar(100) NOT NULL,
SeaID tinyint NOT NULL, -- REFERENCES Sea (SeaID),
CountryID nchar(2) NULL, --sometimes NULL!!!
LocaleID nvarchar(3) NULL, --often NULL!!!
FOREIGN KEY (CountryID, LocaleID) REFERENCES Locale (CountryID,
LocaleID)
)
go
insert into country values (1,'usa')
insert into locale values (1,'tx','Texas')
insert into port values (1,'Port Arthur',0,1,'tx')
insert into port values (2,'Houston',0,1,null)
insert into port values (3,'Dallas',0,null,null)
David|||Thanks, David. You're right. My problem was that what I thought were
NULLs in my source data table turned out to be blanks--not the same
thing. Once I replaced them with NULLs, everything was fine.
As for CELKO's comment about ISO Country codes being CHAR(3), there's
both a 2- and 3-character ISO standard. My example just happened to
have the 2-character version.
-Dan|||Go with the 3; the 2 is about to be deprecated becuase we just have too
many countries declaring themselves.|||If only it were that simple :) My real Country table actually has five
different code columns (including 2- *and* 3-digit ISO) because users
will be getting their country code data from several different
sources/databases, all of which use their own codes. Of course, some of
these source databases have country codes for entities the ISO doesn't
even consider countries.
The result, a big messy normalization problem. Don't you just love when
people ignore standards?
Anyway, even though it seems unlikely we'll go from 243 to 676
countries anytime soon, the 3-digit codes also have the advantage of
being more human readable. E.g., with two characters, Canada = CA and
China = CN; with three, Canada = CAN and China = CHN.
-Dan|||> Go with the 3; the 2 is about to be deprecated becuase we just have too
> many countries declaring themselves.
Looks pretty current to me:
http://www.iso.org/iso/en/prods-ser...n1.
html
Can you post your source that states the 2 code is going to be depreciated?
It will cause absolute confusion over here because travelling on the content
you need to have a sticker with your country code on your car and to my
knowledge Great Britian is still GB.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1148150829.923946.28160@.u72g2000cwu.googlegroups.com...
> Go with the 3; the 2 is about to be deprecated becuase we just have too
> many countries declaring themselves.
>|||Some stickers are 1,2 or 3 characters
I = Italy
F = France
NL = the Netherlands
SLO =Slovenia
I don't think those follow any ISO standards
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Makes matters worse, I wonder what standard us Europeans use then for that?
The only useful thing I found was from the US CIA site:
http://www.cia.gov/cia/publications...appendix-d.html and
there is no mention of 2 code being depreciated, to make matters even more
interesting ISO 3166 has 3 versions, 2 letter, 3 letter and 3 digit!
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"SQL Menace" <denis.gobo@.gmail.com> wrote in message
news:1148222151.073946.222330@.i40g2000cwc.googlegroups.com...
> Some stickers are 1,2 or 3 characters
> I = Italy
> F = France
> NL = the Netherlands
> SLO =Slovenia
> I don't think those follow any ISO standards
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||On Sun, 21 May 2006 17:48:38 +0100, Tony Rogerson wrote:
> to make matters even more
>interesting ISO 3166 has 3 versions, 2 letter, 3 letter and 3 digit!
"The nice thing about standards is that there are so many of them"
-- Unknown
Hugo Kornelis, SQL Server MVP
Saturday, February 25, 2012
Reduce view state size
We are having problems with SQL 200 Reporting services with large view states
being passed to the browser (up to 20Mb). Is there any way of reducing the
view state size used by reporting services, or any way of storing view state
in a database rather than having it passed to the browser with every request?
Thanks,
SJHHi SJH,
I would like to know does this issue appeared on a specified report? If so,
what does this report contained? Does it contain any large amount of text?
It's weird that your reporting services using so much ViewState. How did
you figure out this behavior? Have you got any error message?
Please let me know the information so that I can provide further assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||I've tried to checked on the exact circumstances for this problem, but can't
speak to the individual who reported the problem today.
The problem seems to occur when creating a new subscription for a report.
It does not happen on all reports. I'm not yet sure if it is isolated to a
single report.
S
"Wei Lu [MSFT]" wrote:
> Hi SJH,
> I would like to know does this issue appeared on a specified report? If so,
> what does this report contained? Does it contain any large amount of text?
> It's weird that your reporting services using so much ViewState. How did
> you figure out this behavior? Have you got any error message?
> Please let me know the information so that I can provide further assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hi SJH,
Is the subscription a Data driven subscription or a regular subscription?
Have you got any error message when you create the subscription? What's the
report when you try to create the subscription?
Please let me know the result so that I can provide further assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support|||Hi SJH,
How is everything going? Please let me know if you have any questions or
concerns. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
being passed to the browser (up to 20Mb). Is there any way of reducing the
view state size used by reporting services, or any way of storing view state
in a database rather than having it passed to the browser with every request?
Thanks,
SJHHi SJH,
I would like to know does this issue appeared on a specified report? If so,
what does this report contained? Does it contain any large amount of text?
It's weird that your reporting services using so much ViewState. How did
you figure out this behavior? Have you got any error message?
Please let me know the information so that I can provide further assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||I've tried to checked on the exact circumstances for this problem, but can't
speak to the individual who reported the problem today.
The problem seems to occur when creating a new subscription for a report.
It does not happen on all reports. I'm not yet sure if it is isolated to a
single report.
S
"Wei Lu [MSFT]" wrote:
> Hi SJH,
> I would like to know does this issue appeared on a specified report? If so,
> what does this report contained? Does it contain any large amount of text?
> It's weird that your reporting services using so much ViewState. How did
> you figure out this behavior? Have you got any error message?
> Please let me know the information so that I can provide further assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hi SJH,
Is the subscription a Data driven subscription or a regular subscription?
Have you got any error message when you create the subscription? What's the
report when you try to create the subscription?
Please let me know the result so that I can provide further assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support|||Hi SJH,
How is everything going? Please let me know if you have any questions or
concerns. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
Subscribe to:
Posts (Atom)