I'm creating a database to store words. The intended usage is to query the database to find a word matching a set of filters. E.g. if I wanted to find a word with < 10 letters, < 3 syllables, rhymes with 'at' and matches the tag 'animal', it would return 'cat', 'bat', 'wombat'...

Unnormalised, the table would look like this: (primary key is marked by PK, contextual information is given in square brackets)

    WordID PK,
    AltSpelling, [another accepted variant e.g. "color"/"colour"]
    Rhyme, [the rhyme part of the word, e.g. for "wombat" the rhyme is "at"]
    Pronunciation, [how the word is pronounced in IPA]
    Tags, [tags relate to meaning of the word, e.g. "apple" would have the tags "round", "fruit", "plant" etc.]
    Categories, [categories are how the word would be hierarchically categorised, e.g. cat comes under the category "animal"]
    WordClasses [the word class that a word can be used as, e.g. "rest" can be Noun and Verb]

This is the Entity-Relationship diagram to describe how the database should work:

Word database Entity-Relationship diagram

And finally, this is the solution I've come up with for a normalised database design. FK indicates foreign key, CPK is Composite Primary Key.

Word(WordID PK, Word, OtherSpelling, RhymeID FK, SyllableCount)
Rhyme(RhymeID PK, Rhyme)
Pronunciation(PronID PK, WordID FK, Pron, DialectApplicable [the dialect in which the pronunciation is used, e.g. RP, SE, BrE, AmE])
Tag(TagID PK, TagName, TagReferentCount [the number of words to which the tag applies])
WordTag(WordID CPK FK, TagID CPK FK)
Category(CategoryID PK, CategoryName, CategoryReferentCount [cf. TagReferentCount for categories])
WordCategory(WordID CPK FK, CategoryID CPK FK)
Class(ClassID PK, ClassName, ClassReferentCount [cf. TagReferentCount for word classes])
WordClass(WordID CPK FK, ClassID CPK FK)

The question is twofold: is this database design fully normalised? And more importantly, is it sound? I have no practical experience with databases, so I'd like to know if I've made any mistakes, or if the design can be improved/optimised.

  • It is usual to have the crows-feet at the entity type which will have the foreign key, as you do for e.g. Rhyme / Word. The relationships to WordTag, WordCategory and WordClass are the wrong way around. – Michael Green Jun 25 '14 at 11:53
  • Oh yeah, I see why now. – Lou Jun 25 '14 at 12:38
  • 3
    full normalization is not always desirable, especially for a database with relatively few inserts compared to the number of reads. – Dan Pichelman Jun 25 '14 at 13:02
  • OT, but "at" isn't enough to determine rhyme; e.g. meat vs wombat :) – Jack Jun 25 '14 at 13:53
  • Yeah, I'd indicate by IPA, I just wanted to simplify – Lou Jun 25 '14 at 14:05

is it fully normalised? no.
are there problems with it? possibly yes.

Just one example: "otherspelling" as a field in the word table breaks both normalisation AND is a problem.
What if a word has multiple alternative spellings?

And oh, you shouldn't aim for complete normalisation. Complete normalisation is itself a potential problem, especially for performance.
Normalise where it makes sense, denormalise where it makes sense.

  • I did consider that, but decided that there would never be a practical need to list a third variant spelling, although apparently words with at least three variants do exist – Lou Jun 25 '14 at 11:35
  • If there were other flaws you identified with the design, I'd appreciate it if you explained them as well :) – Lou Jun 25 '14 at 11:36

The most obvious violation of normalization in your DB are the reference counting attributes - they introduce redundant information and could get "out of sync" with the real number of references.

"OtherSpelling" might be a violation of normalization, or it might not, this depends indeed on the data you are going to store.

I think it is debatable if, since entities like Tag, Category or Otherspelling are words for themselves as well, you should really store them in separate tables, or instead use a back reference from the WordTag table or WordCategory table to the Word table.

  • You think it would be better to calculate the number of referents whenever needed, rather than storing a number in the table? – Lou Jun 25 '14 at 11:38
  • I read this question, and I would have thought that the latter approach is more appropriate for the database, because it will potentially be on a very large scale. Producing a new count every time might affect performance, I would have thought. – Lou Jun 25 '14 at 11:41
  • 3
    @LeoKing: I don't say it is "better", I wrote "it is a violation of normalization". Sometimes normalization is better, sometimes it is not. But as a general rule never optimize beforehand, before you have actually measured the performance of a real implementation of your DB. – Doc Brown Jun 25 '14 at 11:45
  • 3
    @LeoKing: I suggest you try to initially implement the database without the reference counts. Then you start implementing the application(s) you have in mind, and see where you have the requirements for using or displaying ref counts. When those application shows a performance bottleneck in that area, you check if you have indexed your DB properly. And if that's the case and you are very sure that ref counts will solve your bottleneck, then you add them afterwards. – Doc Brown Jun 25 '14 at 12:16
  • 1
    In theory storing a count of something is ok, but in practice, that value is often wrong. The only way to enforce that it's correct is to have it be a calculated field in the first place. It is usually more important to be correct than to be fast. – user1068 Jun 25 '14 at 16:23

is this database design fully normalised?

There are many normal forms. This design is mostly 3NF, except for the three ReferenceCount columns. As such it accords with common practice in the software development industry.

is it sound?

Yes, given your comments to the other answer, I wouldn't be upset if one of my developers suggested this as a schema.

I'd question if there should be another table between Word and Rhyme. Do combat and wombat rhyme on the "at" or the "om" or both? Would SyllableCount vary with pronunciation?

  • Good points about pronunciation and rhyme, I need to think about that more. – Lou Jun 25 '14 at 12:11

Model is not normal

  • OtherSpelling should be a separate entity named Spelling
  • DialectApplicable should be a separate entity named Dialect
  • TagReferentCount should be calculated
  • CategoryReferentCount should calculated
  • In WordTag, WordID and TagID should be separate FK besides being CPK
  • In WordCategory, WordID and CategoryID should be separate FK besides being CPK
  • In WordClass, WordID and ClassID should be separate FK besides being CPK
  • ClassReferentCount should be calculated
  • In the chart, cardinality is inverted for the WordTag, WordCategory and WordClass tables, (trident should be on the N:N table)
  • Everything sounds reasonable, bar one point I don't understand: "DialectApplicable should be a separate entity named Dialect". Why? Won't a pronunciation only have one predominant dialect to which it belongs? – Lou Jun 25 '14 at 12:44
  • Well, some things are pronounced the same in multiple dialects, but then I suppose I'd put for that record: "AmE/BrE" or "UK/US" or "All" or something. So I can perhaps see how this violates normalisation, but I don't think I'll change it. – Lou Jun 25 '14 at 12:46
  • 2
    @LeoKing Just be sure to always write the dialects the same way. – Tulains Córdova Jun 25 '14 at 13:09
  • If you ever want to do anything with the words by dialect (e.g., get me all AmE words starting with the letter J), then storing the list of dialects as a "/"-separated string is going to cause headaches, even if you always write the dialects in the same way. – mmitchell Jun 25 '14 at 21:26
  • @mmitchell You are right. It's a bad idea. I stick to it being a table. – Tulains Córdova Jun 25 '14 at 21:28

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.