4

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?

4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.