Lemon-Filling schema works!

I created my schema for lemon-filling, added some data and tried my query. It worked on the first attempt. I was overjoyed. : )

Here we have all the data in their tables. Our demo data is just 3 terms: the 'username ', 'password ', and intro text. It is in English and German. There are two pages: 'sign_in' and 'overview'.

mysql> SELECT * FROM locale;
+-----------+---------+
| locale_id | value   |
+-----------+---------+
|         1 | English |
|         2 | Deutch  |
+-----------+---------+

mysql> SELECT * FROM terms;
+----------+---------------+
| terms_id | value         |
+----------+---------------+
|        1 | lname         |
|        2 | lpass         |
|        3 | welcome_blurb |
+----------+---------------+

mysql> SELECT * FROM rosetta;
+------------+-----------+----------+-------------------------------------------------+
| rosetta_id | locale_id | terms_id | value                                           |
+------------+-----------+----------+-------------------------------------------------+
|          1 |         1 |        1 | Username:                                       |
|          2 |         2 |        1 | Benutzername:                                   |
|          3 |         1 |        2 | Password:                                       |
|          4 |         2 |        2 | Passwort:                                       |
|          5 |         1 |        3 | Welcome to appland! Please log in below.        |
|          6 |         2 |        3 | Welcome to appland Bitte melden Sie sich unten. |
+------------+-----------+----------+-------------------------------------------------+

mysql> SELECT * FROM page;
+---------+----------+
| page_id | value    |
+---------+----------+
|       1 | sign_in  |
|       2 | overview |
+---------+----------+

mysql> SELECT * FROM page_group;
+---------------+---------+----------+
| page_group_id | page_id | terms_id |
+---------------+---------+----------+
|             1 |       1 |        1 |
|             2 |       1 |        2 |
|             3 |       1 |        3 |
+---------------+---------+----------+

Here are the queries. Note all we give it in the where clause is the page we want, and the locale id.

mysql> SELECT terms.value,
    ->    rosetta.value       
    -> FROM locale
    -> RIGHT JOIN rosetta ON locale.locale_id=rosetta.locale_id
    -> INNER JOIN terms ON terms.terms_id=rosetta.terms_id
    -> INNER JOIN page_group ON page_group.terms_id=rosetta.terms_id
    -> INNER JOIN page ON page.page_id=page_group.page_id
    -> WHERE page.value='sign_in' AND locale.locale_id=1
    -> ORDER BY terms.terms_id;
+---------------+------------------------------------------+
| value         | value                                    |
+---------------+------------------------------------------+
| lname         | Username:                                |
| lpass         | Password:                                |
| welcome_blurb | Welcome to appland! Please log in below. |
+---------------+------------------------------------------+

mysql> SELECT terms.value,
    ->    rosetta.value       
    -> FROM locale
    -> RIGHT JOIN rosetta ON locale.locale_id=rosetta.locale_id
    -> INNER JOIN terms ON terms.terms_id=rosetta.terms_id
    -> INNER JOIN page_group ON page_group.terms_id=rosetta.terms_id
    -> INNER JOIN page ON page.page_id=page_group.page_id
    -> WHERE page.value='sign_in' AND locale.locale_id=2
    -> ORDER BY terms.terms_id;
+---------------+-------------------------------------------------+
| value         | value                                           |
+---------------+-------------------------------------------------+
| lname         | Benutzername:                                   |
| lpass         | Passwort:                                       |
| welcome_blurb | Welcome to appland Bitte melden Sie sich unten. |
+---------------+-------------------------------------------------+

I love it; data is totally normalized, yet bundled together in an efficient manner.

Next course of action is to build the UI to input and manage the lemon filling content.

For the curious, here is the SQL to generate the necessary tables.

CREATE TABLE IF NOT EXISTS locale (
    locale_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
    value VARCHAR(90) NOT NULL,
    PRIMARY KEY(locale_id)
) 
ENGINE=InnoDB
CHARACTER SET utf8 
COLLATE utf8_general_ci;
 
CREATE TABLE IF NOT EXISTS terms (
    terms_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
    value VARCHAR(90) NOT NULL,
    PRIMARY KEY(terms_id)
) 
ENGINE=InnoDB
CHARACTER SET utf8 
COLLATE utf8_general_ci;
 
CREATE TABLE IF NOT EXISTS rosetta (
    rosetta_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
    locale_id SMALLINT UNSIGNED NOT NULL,
    terms_id INT UNSIGNED NOT NULL,
    value VARCHAR(19999) NOT NULL,
    PRIMARY KEY(rosetta_id),
    INDEX rlocale_index (locale_id ASC),
    INDEX rterms_index (terms_id ASC)
) 
ENGINE=InnoDB
CHARACTER SET utf8 
COLLATE utf8_general_ci;
 
CREATE TABLE IF NOT EXISTS page (
    page_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
    value VARCHAR(90) NOT NULL,
    PRIMARY KEY(page_id)
) 
ENGINE=InnoDB
CHARACTER SET utf8 
COLLATE utf8_general_ci;
 
CREATE TABLE IF NOT EXISTS page_group (
    page_group_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
    page_id INT UNSIGNED NOT NULL,
    terms_id INT UNSIGNED NOT NULL,
    PRIMARY KEY(page_group_id),
    INDEX pgpage_index (page_id ASC),
    INDEX pgterms_index (terms_id ASC)
) 
ENGINE=InnoDB
CHARACTER SET utf8 
COLLATE utf8_general_ci;