Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I'd recommend the "relational" approach, i.e. separate translation table(s). Consider doing it like this:</p> <p><img src="https://i.stack.imgur.com/M6Szl.png" alt="enter image description here"></p> <p>This model has some nice properties:</p> <ul> <li>For each multi-lingual table, create a <em>separate</em> translation table. This way, you can use the fields appropriate for that particular table, and the translation cannot be "misconnected" to the wrong table.</li> <li>The existence of the LANGUAGE table and the associated FOREIGN KEYs, ensures that a translation cannot exist for non-existent language, unlike the XML.</li> <li><a href="http://www.codesynthesis.com/~boris/blog/2012/04/12/explicit-sql-delete-vs-on-delete-cascade/" rel="nofollow noreferrer">ON DELETE CASCADE</a> referential action will ensure no "orphaned" translation can be left behind when a language is removed, unlike the XML.</li> <li>While XML may be faster in simpler cases, I suspect JOIN is more scalable when the number of languages grows.<sup>1</sup> In any case, <strong>measure</strong> the difference and decide for yourself if it's significant enough.</li> <li>Separate fields such as NAME and DESCRIPTION may be easier to index. With XML, you'd probably need a DBMS with special support for XML, or possibly some sort of full-text index.</li> <li>Fields such as NAME and DESCRIPTION will likely be just regular VARCHARs. OTOH, putting them together may produce XML too large for a regular VARCHAR, forcing you to use a CLOB/BLOB, which may have its own performance complications.</li> <li>If your DBMS supports clustering (see below), the whole translation table can be stored in a single B-Tree. XML has a lot of redundant data (opening and closing tags), likely making it larger and less cache-friendly than the B-Tree (even when we count-in all the associated overheads).</li> </ul> <p>You'll notice that the model above uses <a href="https://stackoverflow.com/a/10658665/533120">identifying relationships</a> and the resulting PK: {LANGUAGE_ID, TABLEx_ID} can be used for <a href="http://use-the-index-luke.com/sql/clustering/index-organized-clustered-index" rel="nofollow noreferrer">clustering</a> (so the translations that belong to the same language are stored physically close together in the database). As long you have <em>few</em> predominant (or "hot") languages, this should be OK - the caching is done at the database page level, so avoiding mixing "hot" and "cold" data in the same page avoids caching "cold" data (and making the cache "smaller").</p> <p>OTOH, if you routinely need to query for many languages, consider flipping the clustering key order to: {TABLEx_ID, LANGUAGE_ID}, so all the translations of the same row are stored physically close together in the database. Once you retrieve one translation, other translations of the same row are probably already cached. Or, if you want to extract multiple translations in the single query, you could do it with less I/O.</p> <hr> <p><em><sup>1</sup> We can JOIN just to the translation in the desired language. With XML, you must load (and parse) the whole XML, before deciding to use only a small portion of it that pertains to the desired language. Whenever you add a new languages (and the associated translations to the XML), it slows down the processing of existing rows even if you rarely use the new language.</em></p>
    singulars
    1. This table or related slice is empty.
    1. POAdding information, xml column or new table?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USBranko Dimitrijevic
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POAdding information, xml column or new table?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload