Localization of database data, design question

Localization of database data, design question

Post by Fuzz » Fri, 31 Oct 2008 20:50:52


Hi,
I need a suggestion on database design for culture-aware data.
Here is a demo of my idea about how to do it, please comment:
tables Products, Categories

Products
{
ProductId : int
FKCategorId : int
}
Categories
{
CategoryId : int
}
CategoryCultureAware
{
FKCategoryId : int
CultureCode : int
Category : nvarchar(25)
}

Thank you!
 
 
 

Localization of database data, design question

Post by Uri Diman » Fri, 31 Oct 2008 21:14:19

Fuzzy
Why do you have Category column in CategoryCultureAware table rather than in
Categories?
If you have CultureCode, most likely you have CultureDescription or
whatever, perhaps that could be separated table

 
 
 

Localization of database data, design question

Post by Fuzz » Fri, 31 Oct 2008 22:40:01

I need Category names translated to several languages. "Category :
varchar(50)" is what gets translated in several languages.
If I put it in Categories table, I would not be able to translate it and
bind it to CultureCode.
 
 
 

Localization of database data, design question

Post by Hugo Korne » Sat, 01 Nov 2008 08:46:02


Hi Fuzzy,

The basic idea is about right. I dislike the column names though.


I assume you meant to add that ProductID is the primary key.

I'd call the other column CategoryID. I dislike prefixing names with
codes that describe their type, rolw or whatever. Name it after what it
is, document the role it plays.


I assume you meant to add that CategoryID is the primary key.


I assume you meant to add that CategoryID and CultureCode combine to be
the primary key.

I'd name the columns CategoryID, CultureCode, and CategoryName (or meybe
even CategoryNameLocalized).

And you should add a table of valid cultures, with CultureCode in this
table being a foreign key into that one.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://www.yqcomputer.com/
 
 
 

Localization of database data, design question

Post by Fuzz » Sat, 01 Nov 2008 20:34:02


Your assumptions is exactly what I meant/did. (Great minds think alike :P)
I wrote column names this way to make it more obvious here. I don't practice
that in real thing.

Thank you very much.
 
 
 

Localization of database data, design question

Post by Fuzz » Sat, 01 Nov 2008 21:16:02


Also I would like to ask for your precious comment on the reason for such
design decision? I got strong pressure from several individuals about this
design is quite heavy on the amount of joins which cripples performance.
While I do understand that it may be true, I've noticed some issues with
different approach I was suggested which included hierarchy model within the
same table to cut on table numbers....
Is their fear of joins realistic?
 
 
 

Localization of database data, design question

Post by Hugo Korne » Tue, 04 Nov 2008 07:45:12


Hi Fuzzy,

No. In a properly normalized database, joins are unavoidable and there
is no reason to fear them.

I was about to go off on a tangent with a lengthy reply that might not
even answer your (or rather, their) real concerns at all. So I deleted
it and I will now instead ask you to tell me what design they proposed
as an alternative. I can than tell you if I agree or disagree with them,
and why.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://www.yqcomputer.com/
 
 
 

Localization of database data, design question

Post by Fuzz » Wed, 05 Nov 2008 01:41:41


Yes, my first idea was just as you have described and proposed. However, I
got strong critics from "more experienced database programmer" about my
design not being usable in pratice for heavy join usage. (I do not rely on
his opinion to the fullest as you can see).

Here is the database I've designed according to requirements:
- Products with two types but collectible as one type
- Discounted and Retail products that both join up as Products to be able to
add them to Order collection.
- Orders that have multiple Products
- BonusItems that are added to each of the Products as bonus
- ProductTypes that define a product
- Localizable ProductTypes and BonusItems to several Cultures

This database is actually a small demo of a much larger and more complex
real database. It's made so it assembles all kind of relations needed.

Here is what I came up with:
http://www.yqcomputer.com/

What design they proposed as an alternative is a kind of hierarchy design
which would look like:

(table)
ProductTypes
(columns)
ProductTypeId
ParentProductTypeId (NULL-able)
CultureId
LocalizedName

so those entries with NULL would be original entries usable for referencing
and others for translations...

I didn't like the idea to be honest...
 
 
 

Localization of database data, design question

Post by Hugo Korne » Fri, 07 Nov 2008 08:53:18


(snip)

And neither do I.

Unless I misunderstand this alternate hierarchical idea, they want to
store a producet with a localized name twice in the Products table, the
second one as a hierarchic descendent of the first one? That just
doesn't make sense. A product with a localized name is still a single
product and should hance be in the products table once. Not more, not
less.

As to your design - more understanding of the business of your client is
required to assess whether it's good. That kind of stuff is very hard to
do over a newsgroup.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://www.yqcomputer.com/