数据库设计 - 多个文本字段

Question

I have a table with products and their descriptions in several languages. For now the table structure is like this

tblProducts

productId INT PK AI
productDesc_en VARCHAR
productDesc_de VARCHAR
productDesc_it VARCHAR

and so on. Where en is for English, de is for German

So visitor according to his language setting, sees description on his language.

Just wondering, are there any benefits in storing data like this instead?

tblProducts

productId INT PK AI

tblProductDesc_en

descId INT PK AI
tblProducts_productId INT FK
description VARCHAR

tblProductDesc_de

descId INT PK AI
tblProducts_productId INT FK
description VARCHAR

tblProductDesc_it

descId INT PK AI
tblProducts_productId INT FK
description VARCHAR

The pro(s) i see in this solution:

1. Easier to maintain from DB perspective
2. Less memory used when instantiating object from DB record (since only required language will be stored within an object)

The con(s):

1. Will have to use JOIN(s) to fetch required data which may hit performance;
2. More complicated getters and setters from within a class

Anything else?

Thank you!

Answer 1

I think having only one extra table with a flag for the language would be a good solution very close to normalization providing a more solid db schemma when new language is added.

It would be like this:

CREATE TABLE `language` (
  `prodID`  INT UNSIGNED NOT NULL  ,
  `desc` varchar(30) null  ,
  `lang`  char(2) NOT NULL,
  PRIMARY KEY  (`prodID`,`lang`),
  CONSTRAINT `fk0` FOREIGN KEY (`prodID`)
        REFERENCES `product` (`prodID`)
        ON DELETE CASCADE
) ENGINE=InnoDB ROW_FORMAT=COMPACT;

The foreign key would provide integrity when product are deleted also allowing insertion when product exists only.

The composite primary key will make so that a description for a language and product is stored only once.

In terms of dependency this primary key looks good to me, recalling lectures on db it is good because the non primary key fields are depended to both parts of the primary key, I mean need both parts to be identified.

You have the same field as part of primary key and foreign key, it is like borrowing this part of the primary key.

========== Edit 1 (Nothing changed above) ============== I would replace Null with Not Null on desc field. Then if a product exists and a description not this means no description available. It is my opinion that there is no reason to allow for null desc above.