Edit Question - KnowledgeHub

Title Body

i am looking for opinions if the following problem maybe has a better/different/common solution: I have a database for products which contains the names of the products in english (the default language of this application) and i need translations of the names if available. Currently i have this setup: A product table CREATE TABLE products ( id serial NOT NULL, "name" character varying(255) NOT NULL, CONSTRAINT products_pkey PRIMARY KEY (id) ) and a product localization table CREATE TABLE products_l10n ( product_id serial NOT NULL, "language" character(2) NOT NULL, "name" character varying(255) NOT NULL, CONSTRAINT products_l10n_pkey PRIMARY KEY (product_id, language), CONSTRAINT products_l10n_product_id_fkey FOREIGN KEY (product_id) REFERENCES products (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE ) and i use the following query to retrieve a list of localized products (german in this case) with fallback to the default english names: SELECT p.id, COALESCE(pl.name, p.name) from products p LEFT JOIN products_l10n pl ON p.id = pl.product_id AND language = 'de'; The SQL code is in postgres dialect. Data is stored as UTF-8.

Tags (comma-separated) Cancel