Designing a code table

[JY]

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

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 "CODE_LOCATION". 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 '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

I have 3 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

 

[Guest]

I would use Solution2 -- a seperate table "LANGUAGE_LOCATION" as a junction
table but it does not need [location_desc].

 

[Tim Ferguson]

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.

This is just a common-or-garden one-to-many relationship.

create table Descriptions (
location_id int foreign key references locations,
language_code int foreign key references languages,
formal_text text

constraint pk primary key (location_id, language_code)
)


Hope that helps


Tim F