Best Practices for Better Database Design in MySQL

database-designMySQL

I'm using a MySQL database to store data from my java application.
In java I have two classes:
The VocabularyList class has a name and holds a list of vocabularies.

A Vocabulary has a vocabulary field and a translation field. (E.g. vocabulary=bridge;translation=Brücke)
Currently my database looks like this:

CREATE TABLE IF NOT EXISTS `vocabularylists` (
  `name` varchar(20) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`name`),
  UNIQUE KEY `id` (`id`)
);

CREATE TABLE IF NOT EXISTS `vocabularies` (
  `vocabulary` varchar(20) NOT NULL,
  `translation` varchar(20) NOT NULL,
  `id` int(11) NOT NULL
);

Question:

How can I ensure that if I delete a vocabulary list that all vocabularies with the id of the vocabulary list are also deleted from the vocabularies table?

Is my design bad and if yes, how can I improve it?

Best Answer

I would begin with a little tweak:

In your current design, there is no need to have "id" column on vocabularylists table. I will write it from top of my head, so apologies in advance for any syntactic mistakes. :)

First change:

CREATE TABLE IF NOT EXISTS `vocabularylists` (
  `name` varchar(20) UNIQUE NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);

This way, you will have numeric ID and easily modifiable name of the vocabulary. This will make occasional renaming of vocabulary list a bit easier. But, of course, you can use only name columnt and totally strip out the id.

Second change: You need to connect the vocabularies to vocabularylists with a foreign key constraint. The vocabularies table will look like this:

CREATE TABLE IF NOT EXISTS `vocabularies` (
    `vocabulary` varchar(20) NOT NULL,
    `translation` varchar(20) NOT NULL,
    `list_id` int(11),
    FOREIGN KEY (list_id) REFERENCES vocabularylists(id) ON DELETE CASCADE
);

By creating foreign key, you ensure so-called referential integrity: from now on, your database cannot store vocabulaies that do not belong to a vocabulary list. Also (as ensured by ON DELETE CASCADE) any deletes on vocabularylists automaticcaly result to deletion of corresponding entries on vocabularies.

Third change:

Querying the vocabularies table will be slow. Put indexes on your tables:

CREATE TABLE IF NOT EXISTS `vocabularylists` (
  `name` varchar(20) UNIQUE NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  INDEX `name_idx` (`name`)
);
CREATE TABLE IF NOT EXISTS `vocabularies` (
    `vocabulary` varchar(20) NOT NULL,
    `translation` varchar(20) NOT NULL,
    `list_id` int(11),
    FOREIGN KEY (`list_id`) REFERENCES vocabularylists(`id`) ON DELETE CASCADE,
    INDEX `vocabulary_idx` (`vocabulary`),
    INDEX `translation_idx` (`translation`)
);

This will give you much better performance.

A hint:

You have a very simple structure defined here. With little data, there will be no significant problems with performance. But the only thing you really need - looking at those two db tables - is a key-value store. So instead of a database, you could easily use a plain/CSV/... file which will be loaded on application startup into some in-memory store (at the beginning a hashmap, then something like Redis maybe).

Also a word in one language can have multiple meanings in another. Your database - as it is - cannot store those things. I don't know if it is desired behavior but it will be limiting you in case you are extending your application.

Related Question