Do most Database Architects work in only one language (English, Spanish, German, Russian, Greek, Arabic, Hindi, Japanese) or at least think in only one language?

The reason that I ask this question is that many people here on DBForums keep raising the idea of "use Natural Keys, they save table lookups" as though this idea made sense, when to me that is gibberish. The only way that I know to cope with multiple languages is to have some sort of surrogate key that links to a table that is directly releated to the concept, then have a one to many table to look up the associated verbage to go with the concept.

Language is a presentation issue, but if you want consistant presentation then the language needs to be stored in a central place (like the associated database) so that it is the same on screens, reports, export files, etc. It can be handled as "one off" options each time you need it (so every report may refer to a thing by its own name), but at least to me that seems to be the road to madness.

Am I missing something in terms of design, am I the only one that has to cope with multiple languages, or am I just further off base than normal this morning?

-PatP

There is no hard and fast answer to this, there are only incorrect applications of the theory!

How often have we seen threads where a user says they want to delete all duplicate records because they've simply assigned a surrogate key and thought that was enough?

I have no beef with surrogate keys as long as a unique constraint is used with it! If you're using "lookup tables" with an integer surrogate key and a descriptor field; are you ever going to want to have the same descriptor more than once?

Pat - you are kidding surely? Are you really serious you want to start this thread?

I like to go au naturel by default but I don't deal with web clouds and internationalisation issues and it is not a dogma for me.


Do you mean people assert that a db using natural keys requires less tables (i.e. no lookup tables) or that queries can be satisfied with less IO?

pootle flump ur codings are working excelent.

I think the implication is that you do not have to perform a join to retrieve the "lookup value".

The same number of lookup tables will exist; how else do you enforce a foreign key constraint on the "offending" field?

If it is a "true" lookup table (i.e. no non-key values) and therefore not a true entry then you can enforce integrity with a CHECK constraint. I don't like to but I have seen it done.

Assuming you're interpretation is right then yes that is correct - certain queries on dbs built using natural keys can result in less IO than if acting on a db built using surrpogate keys. Of course, the converse is true too, although this is more due to physical rather than logical issues

pootle flump ur codings are working excelent.

There is no such thing as a lookup table!

This shall be my new mantra!

I knew you would say that - that's why I used "quotes" Anyhoo - Pat meant IO so irrelevant to this (but not to the GOALS of agile development ).

pootle flump ur codings are working excelent.

If you are not using a One True Lookup table, you are not being Agile enough. you want your developers sitting around making all sorts of lookup tables for State, "yes/no", and "true/false/FILE NOT FOUND"?


oh, wait. I need to add this:

;-)

of course, a properly chosen collection of natural keys will simplify the whole language issue

e.g.
Men = Male (english)
Fen = Female (english)
Hfr = Homme (french)
Ffr = Femme (french)
Msp = Macho (spanish)
Hsp = Hembra (spanish)
etc.

that way, you only need one "language-aware" gender entity table (notice i did not call it a lookup table)

then you would simply use the correct natural key in the People table



oh, wait, it has to be called the People_Persons_Personas_Leute table

damn, pat, now i'm curious -- you go to pains to get your codes right, but what language do you use to name your tables? i bet you wuss out and use english, right?

I've only ever built one multilingual system, this feature was added after the go-live and it consisted of simply replacing as much text as possible with icons and then having simple lookups for the titles and menus that remained. It catered for a bunch of European languages but, a year after introducing this feature, 99% of the users were still using English as their default language. It felt like a waste of effort at the time.

Please bear in mind I'm exercising a great deal of self control not entering this debate

Mike

"one man enters, the other man leaves"

oh wait, it was "two men enter, one man leaves"

in any case, i'm now outta here

Rudy is correct, English is my native language, the agreed upon language used internally by all of the developers and staff, and the default language used when there isn't a localization for a word, phrase, or other text.

My intent was to find out if I'm a "minority of one" in terms of looking at language, laws, units of measure etc as variables instead of constants. It appears that I'm the only one here that thinks the way that I do and sees a dozen languages as a normal thing, but I guess that doesn't surprise me.

-PatP

Pat - are you making a general point about surrogates or specifically regarding language? And did you just chuck the "lookups" bit in there as an afterthought? I have nothing to say on the variables Vs constants side of things - that's a logical problem and I don't want to get into a discussion about the logical merits of surrogates and natural keys. I think I know what your main thrust is here but I don't know if you have a wider point to make too.

pootle flump ur codings are working excelent.