Database Structure Inquiry

bmaciag

I'm creating a php/mysql driven word game similar to the game TextTwist. Essentially, a word will be provided to the player, and the player has to create as many words as possible using only the letters within the provided words.

For example:

Provided word = chicken
Some possible words = hick, chick, nick, neck, etc.

Now, I have a database full of english dictionary words. When a user attempts to submit a word, it will check the dictionary db to see if the word exists, and if so, it will be accepted. When a word is successfully found, I also want to be able to save that word association. So for example, if I submit the word "neck," and it checks out as a valid word, I want to save it in the database so that future players who get the word 'chicken' will see that a 4 letter word can be made, and so 4 empty boxes will be provided. A set of empty boxes will be shown for every word association that has previously been submitted.

If you're still with me here, my question is how should I design the database to handle this sort of functionality?

For example,

id | word | associations

As a rule of thumb, I was always taught that each column should only contain one value. How can I design my database, other than the method above so that there isn't an "array" of values for associations?

laserlight

I would suggest two tables:
Dictionary: id, word
Associations: id, word_id, assoc_word_id

Then your example would become something like this:

Dictionary:
id | word
-------------
5  | chicken
6  | pickle
7  | dumpling
8  | neck
9  | nick
10 | chick
11 | lick
12 | lip
13 | elk
14 | ping
15 | dump
16 | lump

Associations:
id | word_id | assoc_word_id
----------------------------
1 | 5 | 8
2 | 5 | 9
3 | 5 | 10
4 | 6 | 11
5 | 6 | 12
6 | 6 | 13
7 | 7 | 14
8 | 7 | 15
9 | 7 | 16

bmaciag

That is something I was looking into. My question is:

In the example provided, the associated words are stored merely as an id number. Would it be less resource intensive to save the associated word as text for selects, so that when an association is grabbed, I don't have to then reference the dictionary table?

For example

Associations:

id | word_id | assoc_word_id

1 | 5 | neck
2 | 5 | nick
3 | 5 | chick
4 | 6 | lick

etc..

Or is it actually LESS resource intensive to save it as an id for the select statements? I'm not so worried about database size, but I am worried about using a lot of process. And as I am not very familiar with db optimization, I am unsure here.

Similarly, would having the database in my original example be the least resource intensive? I don't mind using the comma separated values as a column, but if the other methods are more resource efficient, that is that I will do.

id | word | associations

laserlight

In the example provided, the associated words are stored merely as an id number. Would it be less resource intensive to save the associated word as text for selects, so that when an association is grabbed, I don't have to then reference the dictionary table?

I believe the answer is yes, as there would be one less join. On the other hand, you would have data duplication and no longer have a guarantee of referential integrity.

Similarly, would having the database in my original example be the least resource intensive? I don't mind using the comma separated values as a column, but if the other methods are more resource efficient, that is that I will do.

You would have to do more work in PHP.

NogDog

Also, don't sell MySQL short in its ability to process the data. My anagram finder script uses a database table with 173,528 words in it, and it zips right through it.

bmaciag

NogDog wrote:
Also, don't sell MySQL short in its ability to process the data. My anagram finder script uses a database table with 173,528 words in it, and it zips right through it.

I took a look at the anagram finder and am quite impressed with the speed. I'm curious, where did you find your dictionary file? I was able to find a few between 65k-85k words each, but nothing like 173k words. I also looked for ones that exclude hyphens and apostrophes.

NogDog

I don't recall precisely, other than that I found it linked from some site that had a bunch of scripts and dictionaries for use with the Scrabble game. In fact, I don't even remember for sure why I did it in the first place, other than that it was prompted by someone asking something about Scrabble and looking for 7-letter word solutions. 🙂

bmaciag

NogDog wrote:
Also, don't sell MySQL short in its ability to process the data. My anagram finder script uses a database table with 173,528 words in it, and it zips right through it.

I followed your advice here, and instead of making a table the contains all of the associations, I tried a select * statement, and all associations are generated on the fly, as opposed to having that second table. It is remarkably quick. I used the php function count_chars() to accomplish the comparisons, somewhat similar to the way you made your anagram finder. My database is now nice and simple. A column for the id, and a column for the actual word. And I don't even need the column for the id.

Take a look:
http://74.52.161.178/~bmaciag8/dict/findall.php

NogDog

Looks good. I tried the word "relevant" and got about 4-5 screens' worth of words in 0.6 seconds.

One suggestion: you might want to modify the style to get a bit more contrast. 🙂