Table structure question

Databases
1

Currently, I am storing a series of categories in a simple table.

Now I want to use / support other languages for display purposes. so, english will always be pulled from the db as the default language but I may need French or Spanish, for display purposes. its for a cms where I need it to be done in English (so I can manage it), but to be useable easily by people of other languages/nationalities.


create table categories
( category_name, varchar(99) not null primary key
) engine......

Now, I want to store >1 language. I think this is a series of 1:1 relationships.

eg ‘Vegetables’ (in English) is (I think), ‘Legumes’ in French.

I suppose my question is: how to tie the realtioships/values together?

like this:-


create table categories
( category_default
, category_french
, category_german
) engine....

or maybe like this


create table categories
( category varchar(99) not null primary key
, language char(3) NULL
) engine...

although the second thought doesn’t relate values of different languages so if I want the category of ‘carrots’, I may also need to get ‘carrotte’ but I’d be SOL with that structure.

bazz

2

no, because you’ve lost the ability to relate the english name for a category with any other language name for that same category

3

Thank you for your replies guys. I try to make my tables as suggested by Felgall so that I can reduce some joins and also, be able to see in a table, what each FK means, without having to look at the parent table.

However, I want also, to have the db efficiently structured for speedy joins. I guess one method (english FKs) might suit my abillities now but numeric keys may be OK when I am more proficient?

Anyway, sorry for the poor explanation guys. I am flustered because i want this whole thing finished now and now will probably mean in about 8 weeks. :frowning:

So, contrary to how I described, this is to be a look-up table where there should be three cols - a category column, a language_abbr col and a sequencing column.

I want to output all categories for a specific language, in the order determined by the sequencing column.


create table categories
( language_abbr char(3) not null
, category varchar(99) not null
, sequence_number tinyint(2) unsigned zerofill NOT NULL
, primary key (language_abbr, category) 
) engine=innodb.....

sample data


INSERT INTO categories (language_abbr, category)
VALUES
 ('ENG','Vegetables','01')
,('FRE','Légumes,'01')
,('GER','Gemüse','01')
,('ENG','Fruits','02')
,('FRE','Fruits','02')
,('GER','Früchte','02');

does this look reasonable. So, as I might add more categories, I just need to add an extra row for each language/category combo?

bazz

4

if category alone is the PK, the table can hold only one row for vegetables

what’s wrong with that concept? :slight_smile:

if language and category together form the PK, then each category can have multiple languages, but it can have each language only once

what’s wrong with that concept? :slight_smile:

you are providing a specific value for the (composite) PK in the WHERE clause, so this query will ~fly~

sorry, i am a bit confused with that :frowning:

5

pish posh

“far more meaningful” is, once again, hyperbole

compare this –

INSERT INTO categories VALUES
(‘vegetables’,‘ENG’,‘vegetables’)
,(‘vegetables’,‘FRE’,‘légumes’)
,(‘vegetables’,‘GER’,‘Gemüse’)
,(‘radishes’ ,‘ENG’,‘radishes’)
,(‘radishes’ ,‘FRE’,‘radis’)
,(‘radishes’ ,‘GER’,‘Radieschen’);

with this –

INSERT INTO categories VALUES
(15,‘ENG’,‘vegetables’)
,(15,‘FRE’,‘légumes’)
,(15,‘GER’,‘Gemüse’)
,(23,‘ENG’,‘radishes’)
,(23,‘FRE’,‘radis’)
,(23,‘GER’,‘Radieschen’);

you’re not comfortable with SQL, are you :slight_smile:

you do it in one query, selecting the english (default) row and then using a LEFT OUTER JOIN for the other, optional, rows

6

Thanks rudy.

which means that language code must become part of the key

I am a bit confused with that.

My query will be something along the lines of


select language_specific_category 
  from categories
where language = 'FRE'
   and category = 'vegetables'

The PK will be an FK in another table too.

bazz

7

I disagree. Being able to reference what the field represents in English from within the code will be far more meaningful than referencing them by a number that has no meaning outside of the table.

If you substitute numbers then you lose the ability to translate from English into another language by looking up one record. Instead you’d need to do a lookup using the field that isn’t a part of the key first in order to find out what surrogate key represents that category first.

8

the second option is much preferred

to accommodate a new language, you simply want to add new rows, ~not~ new columns

mind you, since you are using the category name itself as the primary key, you will need some way to link them together, and your only choice is the category name itself, which means that language code must become part of the key

so what you will end up with is…

CREATE TABLE categories
( category VARCHAR(99) NOT NULL 
, language CHAR(3) NOT NULL
, PRIMARY KEY ( category , language )
, language_specific_category VARCHAR(99) NOT NULL
);
INSERT INTO categories VALUES
 ('vegetables','ENG','vegetables')
,('vegetables','FRE','légumes')
,('vegetables','GER','Gemüse')
,('radishes'  ,'ENG','radishes')
,('radishes'  ,'FRE','radis')
,('radishes'  ,'GER','Radieschen');

here’s one example where a surrogate key, i.e. a numeric id, might make more sense