3

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)

Word(
    WordID PK,
    Word,
    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]
    SyllableCount,
    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.

Improve this question
11
  • 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, 2014 at 11:53
  • Oh yeah, I see why now.
    – Lou
    Jun 25, 2014 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, 2014 at 13:02
  • OT, but "at" isn't enough to determine rhyme; e.g. meat vs wombat :)
    – Jack
    Jun 25, 2014 at 13:53
  • Yeah, I'd indicate by IPA, I just wanted to simplify
    – Lou
    Jun 25, 2014 at 14:05

4 Answers 4

Reset to default
8

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.

Improve this answer
2
  • 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, 2014 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, 2014 at 11:36
3

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.

Improve this answer
7
  • 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, 2014 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, 2014 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, 2014 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, 2014 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, 2014 at 16:23
3

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?

Improve this answer
1
  • Good points about pronunciation and rhyme, I need to think about that more.
    – Lou
    Jun 25, 2014 at 12:11
3

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)
Improve this answer
6
  • 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, 2014 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, 2014 at 12:46
  • 2
    @LeoKing Just be sure to always write the dialects the same way.
    – Tulains Córdova
    Jun 25, 2014 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, 2014 at 21:26
  • @mmitchell You are right. It's a bad idea. I stick to it being a table.
    – Tulains Córdova
    Jun 25, 2014 at 21:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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