Well, the reality of the situation is that if you use a normalized model of a theoretical many to many link database, you will end up with 60,000,000 link entries. That is why a normalization violation (and a violation of academic orderliness!) is in order. I would probably do something like this.
a) keep your story database as is.
DB -->> Content -->> id, date, author, head, body... (and so on)
b) build your word database as follows
word_word - the word itself - after all it is unique to all other words in the database, no?
word_seqn - an arbitrary integer from 1 to n since there may be multiple records for a word.
These two fields together constitute the primary key, but you should also index just word_word by itself to speed up the SELECT's.
Next, the data:
word_column_count - integer showing how many of the following pairs are in the record
word_story_id - key into the content database
word_story_count - count of total occurence in the story.
So, these paired items are integers and take 8 bytes. Thus 500 of them stored in a record would result in a 4000 byte record, not at all unreasonable, and you could probably run a larger record size than that, say 1000 per record, for a record size of 8000 bytes. Now, if a word appears in all 200,000 stories, there will be exactly 200 records for the word, and reading in all the records would result in about 1.6 megs of memory (more actually if you want to read all the gory details of mysql memory allocation), but still less than if you had to read in 200,000 linked entry records if a word appeared in all stories..
So, by tossing the link file and violating normalization rules, you cut your 60,000,000 records down to a maximum of 60,000 since each record holds a 1,000 entries.
Dealing with arrays of pairs is really a piece of cake in mysql since the fetched array can be viewed as either associative or numeric. You use the word_column_count to know where to add new entries in partially filled records. Or you could zero fill unused entries and blow off the column count.
From a speed perspective, it is much faster to read one record with 1000 entries in it, then it is to read 1000 records, each with 1 entry. And, by blowing off the link file, you eliminate the need to search yet another table to find the links. Besides getting the database down to a manageable size, you'll see a big improvement in search speed.
This approach does have one drawback. You can't use it to find all the words in a story via a db lookup. But, I suppose one could do that by looking at the story, no?
