I have a language table 'CODE_LANGUAGE' where we defines languages.
CREATE TABLE [dbo].[CODE_LANGUAGE] (
[language_id] [smallint] NOT NULL PRIMARY KEY CLUSTERED,
[language_name] [varchar(20)] NOT NULL
) ON [PRIMARY]
GO
I need to design a table for Location codes "CODE_LOCATION". This table need
to have these attributes:
location_id
location_name
location_desc
active_flag
external_value
Problem is that location_desc can be in English or French or Spanish. So
what would be the best way to resolve this problem?
I can think of two solutions:
Solution1:
=======
Add 2 columns in 'CODE_LOCATION' table: "id" column and "language_id"
column. The "id" column will be an identity column and will be the primary
key. The "language_id" column will be foreign key to "language_id" column in
'CODE_LANGUAGE' table.
CREATE TABLE [dbo].[CODE_LOCATION] (
[id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
[location_id] [smallint] NOT NULL ,
[location_name] [varchar(20)] NOT NULL ,
[language_id] [smallint] NOT NULL,
[location_desc] [varchar(255)] NOT NULL ,
[active_flag] [tinyint] NOT NULL ,
[external_value] [varchar(10)] NULL
) ON [PRIMARY]
GO
Solution2:
=======
Create a seperate table "LANGUAGE_LOCATION" which stores description of
location codes in different languages. Its primary key will be foreign key
in 'CODE_LOCATION' table.
CREATE TABLE [dbo].[LANGUAGE_LOCATION] (
[lang_loc_id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
[language_id] [smallint] NOT NULL ,
[location_id] [smallint] NOT NULL ,
[location_desc] [varchar(255)] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CODE_LOCATION] (
[location_id] [smallint] NOT NULL PRIMARY KEY CLUSTERED,
[location_name] [varchar(20)] NOT NULL ,
[lang_loc_id] [int] NOT NULL REFERENCES LANGUAGE_LOCATION(lang_loc_id),
[active_flag] [tinyint] NOT NULL ,
[external_value] [varchar(10)] NULL
) ON [PRIMARY]
GO
Which solution is better and why?
It is my understanding that both solutions are in 3rd normal form. Is it
correct?
Do you guys have any better solution?
ThanksI'm trying to understand the problem better.
>Problem is that location_desc can be in English or French or Spanish. So
>what would be the best way to resolve this problem?
For one location, is there going to be ONE location_desc in ONE
language, or several with one in EACH language?
Roy|||Let's fix that first table. You should have found the ISO language
codes when you did research -- in about 3 seconds with a slow
connection to Google. The codes are CHAR(3).
Is there a very good reason you threw out all the ISO-11179 conventions
about putting the attribute in a postfix? What is that reason?
Oh yes, the "magical UNIVERSAL identity exposed physical locator" that
newbies use to spit on RDBMS and Dr. Codd.
I also see that you want to write with flags like SQL was assembly or C
code. Major mindset error.
Your next mistake will be saying things like "XX_loc_id" not knowing
that an attribute cannot be both an identifier and a location.
One column for each language in the same table?|||On 23 Feb 2006 18:19:38 -0800, "--CELKO--" <jcelko212@.earthlink.net>
wrote:
>One column for each language in the same table?
And when the requirement to add German and Italian comes along, what
then?
Roy 8-)|||"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:e9nsv1d96cbrv3qok72pb3schg8v0dce4m@.
4ax.com...
> I'm trying to understand the problem better.
>
> For one location, is there going to be ONE location_desc in ONE
> language, or several with one in EACH language?
> Roy
For one location there will be several location_desc with one in EACH
language.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1140747578.311798.118740@.v46g2000cwv.googlegroups.com...
> Let's fix that first table. You should have found the ISO language
> codes when you did research -- in about 3 seconds with a slow
> connection to Google. The codes are CHAR(3).
>
I did not know about ISO language codes. After your email I research it and
decided that i dont want to use it. By using my table I can differentiate
between american english and canadian english.
> Is there a very good reason you threw out all the ISO-11179 conventions
> about putting the attribute in a postfix? What is that reason?
>
I did not know about ISO-11179 conventions. I am going to research these
naming conventions.
> Oh yes, the "magical UNIVERSAL identity exposed physical locator" that
> newbies use to spit on RDBMS and Dr. Codd.
>
This is part of Solution1 i come up with. I wrote this email to ask if this
is right approach. My guess is that you dont like Solution1. Am i right?
> I also see that you want to write with flags like SQL was assembly or C
> code. Major mindset error.
> Your next mistake will be saying things like "XX_loc_id" not knowing
> that an attribute cannot be both an identifier and a location.
>
> One column for each language in the same table?
>
For one location there will be several location_desc with one in EACH
language. So if there is Location 'Mississauga' then there could be 3
location_desc for it: one in US English; one in CAN French and one in
Spanish. Keeping this in mind please look at the 2 solutions I come up with
and answer these questions:
1) Which solution is better and why?
2) It is my understanding that both solutions are in 3rd normal form. Is it
correct?
3) Do you guys have any better solution?
Thanks|||>Solution2:
>=======
>Create a seperate table "LANGUAGE_LOCATION" which stores description of
>location codes in different languages. Its primary key will be foreign key
>in 'CODE_LOCATION' table.
>CREATE TABLE [dbo].[LANGUAGE_LOCATION] (
> [lang_loc_id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
> [language_id] [smallint] NOT NULL ,
> [location_id] [smallint] NOT NULL ,
> [location_desc] [varchar(255)] NOT NULL
> ) ON [PRIMARY]
>GO
>For one location there will be several location_desc with one in EACH
>language.
Then the proper solution is a table similar to LANGUAGE_LOCATION in
your second solution, but not quite. I would never, ever, create an
IDENTITY column on that table. You have a perfectly good PK in
(language_id,location_id). I can go along with surrogate keys in
circumstances that justify them, but this seems completely
inappropriate to me.
Roy
.com/story/showArticle.jhtml?articleID=10700814[/url]