We've done a lot of this, and (administrative) users were allowed to fix the translations live. (You still might want a caching layer, but I'm totally down with driving this with a real database and not resource files - it gives you a ton of power to query and find things which need to be translated, etc). I think your schema is probably fine, so I'll just pass on some stuff we learned in the hope that it's useful.
One thing you have left out is phrases with insertion points. In the example below, the order is reversed and the language is still English, but this could very easily be two different languages - pretend this is just two languages who normally put things in a different order.
Hello, <username> you have <x> points!
You've got <x> points to spend, <username>!
In our pre-.NET, we had a routine which did the insertion so the phrases would look like this:
Hello, {0:username} you have {1:points} points!
You've got {1:points} points to spend, {0:username}!
This would obviously simply be used in your code as <%= String.Format(phrase, username, points); %>
or similar
Which helped the translator a bit. But .NET String.FOrmat doesn't support commenting within the format string, unfortunately.
As you say, you would not want to handle that in your php with locale awareness or meta phrases.
So what we had was a master phrase table:
phraseid, english, supplemental info
and a localized table:
phraseid, localeid, translation
You've also assumed with INNER JOINS that the localized versions exist - we tended to leave them out until they were translated, so that query of yours would end up returning nothing at first (not even the default)
If a translation didn't exist, ours defaulted to English, then fellback to code-provided (in case the database didn't have the ID, and it was also clear from the code what phrase identifier "TXT_LNG_WRNNG_INV_LOW" was actually trying to get) - so the equivalent of this query is what we used:
SELECT COALESCE(localized.translation, phrase.english, @fallback)
FROM DUAL
LEFT JOIN phrase
ON phrase.phraseid = @phraseid
LEFT JOIN localized
ON localized.phraseid = phrase.phraseid
AND localized.localeid = @localeid
Obviously, you might get all the things at one time using your page system.
We tended not to link things to the page because they were re-used a lot between pages (and not just in page-fragments or controls), but that's certainly fine.
In the case of our Windows-native apps, we used reflection and a mapping file from control to translation tag so that translation did not require re-compiles (in pre-.NET apps we had to tag the controls using the Tag or other special properties). This is probably a little more problematic in PHP or ASP.NET MVC, but possible in ASP.NET where there is a full-blown server-side page model.
For testing, you can obviously query to find missing translations very easily. To find places which need to be tagged, translate the entire phrase dictionary using pig-latin or Klingon or something like replace every non-space character with ? - the English should stand out and let you know that some naked plaintext has crept into your HTML.
I think that the best for your tag
and posttag
tables is:
- ok for the
tagid
as a primary key
- add an index on your
tag
field (this cover your second query)
- add two index on posttag table like
INDEX(postid,tagid)
and INDEX(tagid,postid)
(this cover your first and second query)
So:
CREATE TABLE `posts` (
`postid` mediumint(9) NOT NULL AUTO_INCREMENT,
`post` varchar(255) NOT NULL,
PRIMARY KEY (`postid`)
);
CREATE TABLE `posttags` (
`postid` mediumint(9) NOT NULL,
`tagid` mediumint(9) NOT NULL,
PRIMARY KEY (`postid`,`tagid`),
UNIQUE KEY `tagid_postid_UQ` (`tagid`,`postid`),
FOREIGN KEY (postid)
REFERENCES posts (postid),
FOREIGN KEY (tagid)
REFERENCES tags (tagid)
);
CREATE TABLE `tags` (
`tagid` mediumint(9) NOT NULL AUTO_INCREMENT,
`tag` char(30) NOT NULL,
PRIMARY KEY (`tagid`),
UNIQUE KEY `tag_UQ` (`tag`)
);
And for your query:
mysql> explain select p.* from tags as t inner join posttags as pt on t.tagid = pt.tagid inner join posts as p on p.postid = pt.postid where t.tag = ?;
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+--------------------------+
| 1 | SIMPLE | t | ref | PRIMARY,tag | tag | 32 | const | 1 | Using where; Using index |
| 1 | SIMPLE | pt | ref | postid,tagid | tagid | 3 | test.t.tagid | 1 | Using index |
| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 4 | test.pt.postid | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+--------------------------+
explain select p.* from posts as p inner join posttags as pt on pt.postid = p.postid inner join tags as t on t.tagid = pt.tagid where p.postid = ?;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
| 1 | SIMPLE | p | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 1 | SIMPLE | pt | ref | postid,tagid | postid | 4 | const | 2 | Using index |
| 1 | SIMPLE | t | eq_ref | PRIMARY | PRIMARY | 3 | test.pt.tagid | 1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
Best Answer
The only way I can think to answer this question is with another example of hasMany that may or may not answer your question.
err, except I should have called the table "comment" but I think the point stands.
I don't know that this really helps you understand anymore than what you already had.
So I'll also share a query that might demonstrate how this works:
which we expect to return many rows, because one post generally has many comments (in theory, of course not all posts will have comments)
Update:
So here's the thing about relations: They are EASY to model via diagram (see the very first tool I pulled out to answer this question) but they are HARD to diagram via text. So instead of trying to define whether we mean 1:M or 0:N, we just write it in "near English" and say what we intend. Here's some alternatives:
As you see they can get quite complicated. This would ... once again! ... indicate a need to refactor your business logic into simpler and smaller chunks.
These can also be called "association types" and may be indicated by verbage like "WrittenBy" (a many to one) or "IsOwnedBy" (one to one? - context is key)
Before I walk away from the "hasMany" concept for the last time (well this is an update, more will likely be written) I want to mention that while it's useful for "plain English descriptions", it's also used in ActiveRecord styled installations. Take, for instance, Ruby. http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html The rest of this post (so far) is taken from that page:
This allows the ActiveRecord ORM to create the data model and to maintain the relationships as specified by the business logic within the program. But I don't write Ruby or Rails so that's as far as I can tell you, I just know that it exists there.