Hi I'm trying to design a database for a dictionary. The words has several fields like meaning, an example, and maybe a picture. but the words that are 'verbs' has three more special fields that the rest does not. So is this a Is-A relation ship? but I tried this:
I made a table for words with all sharing fields and a type field, and a table for verbs. these two tables has a one to one relation. the verb table just has the special fields and the foreign key. so if a user wants to only search for a verb he/she needs to search from all words that has the "type == verb". is this a good idea? Please help me how to design this, Thanks in advance.

  • 1
    You've created a subtype which is a good normalization technique. Sounds like it meets your requirements here... so you should be fine. For a full fledged (e.g. OED) database you might find too many unique elements to create such a design though. – Dave Dec 8 '15 at 19:12
  • @Dave Thanks for the answer, by the way I'm always doubtful about designing database, is there any book you can recommend me for mastering system analysis? – farid_92 Dec 8 '15 at 19:31

I would not create another table for verbs because join operations on big tables (especially multiple ones in the same query) may be expensive. I would create one table, with the shared AND the non-shared fields, AND add another column "isverb", set it to 1 for verbs and 0 for non-verbs. You could also later on add "isadjective" etc.

In this case it just makes sense to do:

SELECT * FROM words where isverb = 1

rather than:

FROM words w 
INNER JOIN verbs v ON w.type = v.id 
WHERE w.type = 2

The application is a dictionary. So it's write once, read a zillion times and so I don't personally see a need for a complicated database design. You essentially just need very fast reads which you can achieve without joins to multiple tables

  • 3
    Have you measured how expensive joins are? I thought it was one of the core concepts of any RDBMS and therefore one of the best optimized operations in the IT world. – dezso Dec 10 '15 at 14:47
  • 2
    And what about the other word types and the type-specific columns? – dezso Dec 10 '15 at 15:54
  • 1
    And in this case you'll get a (very) sparse table. This may be good or bad, but in most cases I'm aware of it's not really a desirable design. – dezso Dec 11 '15 at 11:45
  • 2
    If you add all your reasoning to your answer and remove 'join operations are expensive', you are eligible for an upvote from me - even if I'm not sure it will be faster this way than that way (this is true in both directions, performance tests are always welcome). – dezso Dec 11 '15 at 14:19
  • 1
    Nice approach but this will add lots of null data, So you are saying that this way is still better than Join costs, right? – farid_92 Dec 11 '15 at 21:13

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.