smenatic mysql queries (xml)

[>triceron]

Hi,

 

I started work on a semantic(1) database. I also thought that a nice feature be the database being multilingual, where the languages would be determined by using a xml tags as well. Example - let\'s say I\'m describing a website about shoes and on the propper row In the decription collumn I have the following:

 

<english>Shoemakers with <time>20</time> years of experience.</english> <german>Schumacher mit <time>20</time> Jahre Erfahrung.</german>

 

Is there a way to display a part of the description cell for this webiste? Say that if language is chosen to be english, the query would display only

 

<english>Shoemakers with <time>20</time> years of experience</english>

 

if the language is german it would show only

 

<german>Schumacher mit <time>20</time> Jahre Erfahrung.</german>

 

Can this be done with a mysql query? (the project works on mysql/php, so using php wouldnt be too bad either...)

 

...or do you have any other ideas on how to do this? Adding more culomns wouldnt be so good (there are now 4 columns which can have up to 15 different language mutations - 4*15=60 columns is pretty ugly in my eyes). Should you suggest creating 15 tables.. well managing the whole thing wouldnt be as easy as it\'s now..

 

any ideas?

 

 

 

 

 

 

-----

1: This means, that xml elements are used to let scripts understand the meaning of, say numbers. There is a difference to the term \"2000\" if you have sentences like:

- The two cities are <distance>2000</distance> miles away.

- There was something to the year <date>2000</date>.

[Barand]

Don\'t have separate columns, have separate rows in a table

id  |  sentence_id  |   language   |  sentence  

1   |         1          |     Eng         |  <english>Shoemakers with <time>20</time> years of experience.</english> 

2   |         1          |     Ger         |  <german>Schumacher mit <time>20</time> Jahre Erfahrung.</german> 

 

hth