CODEWITHSUNDEEP
sqldatabase-designrelational-databasemultilingualhierarchical-data
Pushkin
Pushkin

Reputation: 63

Multilingual database design

I'm trying to design a database schema for a multilingual application. I have so far found a sample from this address. http://fczaja.blogspot.com/2010/08/multilanguage-database-design.html

But I haven't understood this sample. Should I insert Id value on app_product first? How can I know that these values are true for ProductId on app_product_translation?

CREATE TABLE ref_language (
  Code Char(2)NOT NULL,
  Name Varchar(20) NOT NULL,
  PRIMARY KEY (Code)
);

CREATE TABLE app_product (
 Id Int IDENTITY NOT NULL,
 PRIMARY KEY (Id)
);

CREATE TABLE app_product_translation (
 ProductId Int NOT NULL,
 LanguageCode Char(2) NOT NULL,
 Description Text NOT NULL,
 FOREIGN KEY (ProductId) REFERENCES app_product(Id),
 FOREIGN KEY (LanguageCode) REFERENCES ref_language(Code)
);

Upvotes: 1

Views: 752

Answers (1)

joshp
joshp

Reputation: 1892

It looks like SQLServer code, proceeding on that assumption.

Yes you must insert the app_product first. But you cannot insert the id column's value. It is assigned automatically, because it is an identity column.

Two things you can check out...to find the identity column's value after inserting.

  1. The OUTPUT clause of the INSERT statement. It can return any values that are inserted, not just the identity column.
  2. The @@Identity variable. (by far more traditional and popular)
declare @lastid int
insert into x values (1,2,3)
set @lastid = @@identity

insert into y values (@lastid, a, b, c)

Upvotes: 1

Related Questions