In the previous chapter of this story I talked about prototyping my 37 App – an app I’m building to help me learn new, not so common words.
I had mentioned a database that would contain the words, their definitions and usage examples. All that would fit in one table just fine. Or even an xml file stored on the device. But, I want to eventually publish this app, so other people can use it. Which means every user will have their own list of words with different levels of completion. And I want them to be able to use it on multiple devices and have their progress synced. So, a bit more complicated than an xml file.
Part 2: Database design
A couple more decisions to make before designing the database. Where to store the database, and what kind of database format to use. I already have a hosting plan on dreamhost and it comes with MySQL so I’ll be using that. The documentation on how to set up a database is very well written, so points to dreamhost for that one.
Now, I need to have information about the word, which includes definition and one or more use examples, and I also need a user table to keep track of all my users who are using my app. And of course, a cross reference table in between to connect my users with the words and record usage count, status and a couple of dates. Here’s what I ended up with:
Creating the database
The tool I used to generate the diagram above allows me to also export the sql file to create the database. But, the only options are t-sql and ansi sql, which are not quite what I was needing for MySQL, so I had to make a few changes.
CREATE TABLE word( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, word varchar(300), definition VARCHAR(5000) )
CREATE TABLE usage_example( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, word_id int NOT null, usage_string VARCHAR(5000), FOREIGN KEY (word_id) REFERENCES word(id) )
CREATE TABLE appuser ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, username varchar(100) not null, name varchar(200) , email varchar(200) )
CREATE TABLE xref_word_user ( word_id int NOT null, user_id int NOT null, status varchar(50), count int default 0, date_start timestamp, date_last_updated timestamp, FOREIGN KEY (word_id) REFERENCES word(id), FOREIGN KEY (user_id) REFERENCES appuser(id))
Populating the database
Going to have to find myself a source for the words and scrape it. But until then, I’ll manually add a few words to the database.
INSERT INTO word ( word, definition ) VALUES('Abhor', 'to regard with extreme repugnance or aversion; detest utterly; loathe; abominate. '), ('Bilk', 'to defraud; cheat; to evade payment of (a debt); to frustrate; to escape from; elude. '), ('Engender', 'to produce, cause, or give rise to; to beget; procreate; to be produced or caused; come into existence.')
INSERT INTO usage_example ( word_id, usage_string ) VALUES(1, 'I rarely mention Hamas without saying that I abhor its values.'), (1, 'Liberals are supposed to abhor that sort of thing and find less loaded terms where they can. '), (1, 'I abhor the Hamas charter with its anti-Semitic, anti-Zionist, anti-Western, anti-democratic call for a Judenrein Palestine. '), (2, 'The lawsuit alleged that Danone used these claims to bilk consumers out of more than $100 million. '), (2, 'He told me Andrew had tried to bilk $50 million out of Mrs. Mellon'), (2, 'Con artists routinely hack into accounts to impersonate people and bilk money from strangers. '), (3, 'King: We must expunge from our society the myths and half-truths that engender such groundless fears as these. '), (3, 'But their point is to show how strong Putin is rather than engender competition. '), (3, 'Moreover, it will engender even greater dissatisfaction among the population. ')
Running a quick join on the two tables to confirm that my inserts were correct:
SELECT * FROM `word` inner join usage_example on word.id = usage_example.word_id
And the result
Last step, adding myself as a user:
INSERT INTO appuser ( username, name, email ) VALUES('codergrl', 'Mara Stoica', 'marastoica@gmail.com')
Tools used
Quick DBD is a great tool that has a free tier. Very simple to use and when you’re done designing, you can export your design to png, pdf or sql, among other formats. Quite useful.
Your turn
Got questions about the database design or the tools I used? Suggestions? Leave it all in a comment below.
