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:
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.