I have a language table 'CDE_LANGUAGE' where we defines languages.
CREATE TABLE [dbo].[CDE_LANGUAGE] (
[language_id] [smallint] NT NULL PRIMARY KEY CLUSTERED,
[language_name] [varchar(20)] NT NULL
) N [PRIMARY]
G
An example of data in this table is:
language_id language_name
1 USA English
2 CAN English
3 French
4 Spanish
I need to design a table for Location codes "CDE_LCATIN". This table need
to have these attributes:
location_id
location_name
location_desc
active_flag
external_value
Problem is that for each location the Location Description (location_desc)
can be in any Language we supported. So if there is a Location 'Mississauga'
and we have four Languages in the System (USA English, CAN English, French
and Spanish), then there will be four location_desc for Mississauga.
So what would be the best way to resolve this problem?
I can think of two solutions:
Solution1:
Add 2 columns in 'CDE_LCATIN' 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
'CDE_LANGUAGE' table.
CREATE TABLE [dbo].[CDE_LCATIN] (
[id] [int] IDENTITY (1, 1) NT NULL PRIMARY KEY CLUSTERED,
[location_id] [smallint] NT NULL ,
[location_name] [varchar(20)] NT NULL ,
[language_id] [smallint] NT NULL,
[location_desc] [varchar(255)] NT NULL ,
[active_flag] [tinyint] NT NULL ,
[external_value] [varchar(10)] NULL
) N [PRIMARY]
G
Solution2:
Create a seperate table "LANGUAGE_LCATIN" which stores description of
location codes in different languages. Its primary key will be foreign key
in 'CDE_LCATIN' table.
CREATE TABLE [dbo].[LANGUAGE_LCATIN] (
[lang_loc_id] [int] IDENTITY (1, 1) NT NULL PRIMARY KEY CLUSTERED,
[language_id] [smallint] NT NULL ,
[location_id] [smallint] NT NULL ,
[location_desc] [varchar(255)] NT NULL
) N [PRIMARY]
G
CREATE TABLE [dbo].[CDE_LCATIN] (
[location_id] [smallint] NT NULL PRIMARY KEY CLUSTERED,
[location_name] [varchar(20)] NT NULL ,
[lang_loc_id] [int] NT NULL REFERENCES LANGUAGE_LCATIN(lang_loc_id),
[active_flag] [tinyint] NT NULL ,
[external_value] [varchar(10)] NULL
) N [PRIMARY]
G
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?
Thanks

>Do you guys have any better solution? <<

1) Use the IS Language codes instead of making up your own.

2) Use the IS Country codes instead of making up your own.

Why did you not do the *most basic* research? Good programmers always
look for Standards -- ANSI. IS, their own industry, etc. Since we
have Google, there is no reason not to do this, not even the classic
reason of bad programmers -- "I was in a hurry!!'.

3) Data element cannot be named things like "CDE_LCATIN" (which is
it? a code or a location? It cannot be both; codes are abstractions) or
"language_id" (it should be an IS code, not an identifier!)

4) SQL programmers do not use flags; we have carefully designed
encoding and do not work at the binary level of a assembly language
programmer.

5) Is VARCHAR(255) the result of research or did you make it up on the
fly?

CREATE TABLE CountryCodes
(county_code CHAR(3) NT NULL PRIMARY KEY, -- IS
county_name CHAR(15) NT NULL);

CREATE TABLELanguageCodes
(language _code CHAR(3) NT NULL PRIMARY KEY,
language_name CHAR(15) NT NULL);

CREATE TABLE CountryLanguages
(county_code CHAR(3) NT NULL -- IS
REFEENCES CountryCodes(county_code),
language _code CHAR(3) NT NULL
REFERENCES LanguageCodes(language_code),
PRIMARY KEY (county_code, language_code)
spoken_percentage DECIMAL (3,2) NT NULL);

you could use the number of speakers within a country.

>It is my understanding that both solutions are in 3rd normal form. Is it correct? <<

You have other redundancy problems. Getting to 3NF is a bare, minimal
state for a schema. You need at least 5NF and DKNF for anythign to
work in the real world.

wrote:
>Do you guys have any better solution? <<
>
1) Use the IS Language codes instead of making up your own.

2) Use the IS Country codes instead of making up your own.

Why did you not do the *most basic* research? Good programmers always
look for Standards -- ANSI. IS, their own industry, etc. Since we
have Google, there is no reason not to do this, not even the classic
reason of bad programmers -- "I was in a hurry!!'.

3) Data element cannot be named things like "CDE_LCATIN" (which is
it? a code or a location? It cannot be both; codes are abstractions) or
"language_id" (it should be an IS code, not an identifier!)

4) SQL programmers do not use flags; we have carefully designed
encoding and do not work at the binary level of a assembly language
programmer.

5) Is VARCHAR(255) the result of research or did you make it up on the
fly?

CREATE TABLE CountryCodes
(county_code CHAR(3) NT NULL PRIMARY KEY, -- IS
county_name CHAR(15) NT NULL);

Sorry Joe, I cannot resist. Did you not do even the *most basic*
research? Some countries have names longer than 15 characters. For
shame! ;-)

(Could continue with some discussion about how children in "Equatorial
Guinea" are going to die! because of this, but decided it would get too
morbid)

Damien

>Sorry Joe, I cannot resist. Did you not do even the *most basic* research? Some countries have names longer than 15 characters. For shame! ;-) <<

Do not try to resist; all you can do is make me less ignorant. I'll
look again, but I remember CHAR(15) as the limit set for International
Mail addresses. I would like to move to the IS "abbreviation" codes,
but that might be too hard on Postal Workers.