If you have a one-to-many relationship between Producers and Products (in other words, a product can only belong to one producer), than it would make sense to just put a foreign key reference directly in your Products
table:
One-To-Many
create table Producer
(
id int identity(1, 1) not null primary key clustered,
Name varchar(100) not null
)
go
create table Product
(
id int identity(1, 1) not null,
Name varchar(100) not null,
ProducerId int not null foreign key references Producer(id)
)
go
But if there's a chance that this will be a many-to-many relationship, then your best bet would be to use a Join table.
Many-To-Many
create table Producer
(
id int identity(1, 1) not null primary key clustered,
Name varchar(100) not null
)
go
create table Product
(
id int identity(1, 1) not null primary key clustered,
Name varchar(100) not null
)
go
create table ProductProducer
(
ProductId int not null foreign key references Product(id),
ProducerId int not null foreign key references Producer(id)
)
go
-- adding the primary key also ensures uniqueness
alter table ProductProducer
add constraint PK_ProductProducer
primary key (ProductId, ProducerId)
go
If you decide to go with the Join table, you wouldn't need to have an additional key, as the combination of ProductId/ProducerId
would ultimately be unique. You could use them as a composite key, so you wouldn't need that additional Id
field in ProductProducer
.
If I understand your requirements correctly....
I would just use the natural key, LanguageCode-CultureCode ("en-US," for example). It's small enough. (I'm using the entire "en-US" as the primary key to differentiate it from "en-GB," for example.)
CREATE TABLE [dbo].[Language](
[Language] [char](2) NOT NULL,
[Culture] [char](2) NOT NULL,
[LanguageCode] AS (([Language]+'-')+[Culture]) PERSISTED NOT NULL,
CONSTRAINT [PK_Language] PRIMARY KEY CLUSTERED
(
[Language] ASC,
[Culture] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [LanguageCode] UNIQUE NONCLUSTERED
(
[LanguageCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Language_Text](
[LanguageID] [varchar](5) NOT NULL,
[LanguageCode] [varchar](5) NOT NULL,
[LanguageName] [nvarchar](20) NULL,
CONSTRAINT [PK_Language_Text] PRIMARY KEY CLUSTERED
(
[LanguageID] ASC,
[LanguageCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Language_Text] WITH CHECK ADD CONSTRAINT [FK_Language_Text_Language] FOREIGN KEY([LanguageCode])
REFERENCES [dbo].[Language] ([LanguageCode])
GO
ALTER TABLE [dbo].[Language_Text] CHECK CONSTRAINT [FK_Language_Text_Language]
GO
This should allow you to get all articles in en or sv, and you can also query for en-US or sv-SE. Presumably, although it wasn't in your example, you could also query for en-CA, fr-CA, en, fr, or CA.
Edit--I'm sorry, you're right, no search by Culture in my old code. Revamped above, sorry. Here's a sample of content:
Language:
Language-Culture-LanguageCode
en US en-US
sv SE sv-SE
Language_Text:
LanguageID-LanguageCode-LanguageName
en-US en-US English
en-US sv-SE Engelska
sv-SE en-US Swedish
sv-SE sv-SE Svenska
Searching by Culture (Canada):
SELECT test.dbo.Language_Text.LanguageID, test.dbo.Language_Text.LanguageCode, test.dbo.Language_Text.LanguageName
FROM test.dbo.Language_Text INNER JOIN
test.dbo.Language ON test.dbo.Language_Text.LanguageID = test.dbo.Language.LanguageCode
WHERE (test.dbo.Language.Culture = 'CA')
Searching by Language (French):
SELECT test.dbo.Language_Text.LanguageID, test.dbo.Language_Text.LanguageCode, test.dbo.Language_Text.LanguageName
FROM test.dbo.Language_Text INNER JOIN
test.dbo.Language ON test.dbo.Language_Text.LanguageID = test.dbo.Language.LanguageCode
WHERE (test.dbo.Language.Language = 'fr')
Searching by LanguageCode (Swedish):
SELECT LanguageName
FROM [test].[dbo].[Language_Text]
where (LanguageID = 'sv-SE')
Best Answer
Definitely the first one with both Allergen and AllergenTranslation tables.
The reason is that Allergen is likely to be referenced in other places. Say you have a table of medicines each of which is effective against a subset of allergens. What would be the foreign key in this Efficacy table? You don't want that FK to have LanguageId in it at all - the medicine's response is not conditional on the patient's speech. Nor do you want to repeat rows in Efficacy for each language as this is redundancy leading to duplication and errors. Think of the changes required to introduce another language.