Many columns or has many

[darcuss]

Hi

 

I'm looking at a project for uni which is like a simple translation application. I am storing items in a tree structure with each item having a name and description of various languages - for the sake of the project the number of languages need be sizeable. The user logs in with their spoken language and the tree items would be browsed with the correct translation returned accordingly.

 

I'm not sure thus about the db design and performance considerations. I have the choice between having a table with lots of columns:

#item table
|  id  | tree_data_representation |  eng_name  | eng_desc  |   fr_name  | fr_desc  |   ita_name  | ita_desc  |.......

or having two joins:

#item table
|  id  | tree_data_representation |  

#name table
|  item_id  | locale |  name |

#description table
|  item_id  | locale |  description |

or having one join:

#item table
|  id  | tree_data_representation |  

#name_description table
|  item_id  | locale |  name | description|

It occurs to me that having joins for each entry is probably slower, but also there is the potential to having a table with an extremely large number of columns.

 

My research thus far leads me to think partitioning the former might be the best way to go but that subject is totally new to me so I thought I'd ask advice of those more knowledgeable than myself.

 

Thanks in advance!

[darcuss]

- or for that matter I could have a separate table for each language.

[Mchl]

I'd go with third option. JOINs being slow (compared to what?) is a myth spread by people, who only can do full CROSS JOINS. And remember you cannot have unlimited columns in a table, while you can have unlimited (read: very large amount of) rows.

[darcuss]

Hi Mch. Thanks very much for your reply.