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.
-
1You'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.– DaveCommented Dec 8, 2015 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_92Commented Dec 8, 2015 at 19:31
Add a comment
|
1 Answer
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:
SELECT *
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
-
3Have 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. Commented Dec 10, 2015 at 14:47
-
2And what about the other word types and the type-specific columns? Commented Dec 10, 2015 at 15:54
-
1And 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. Commented Dec 11, 2015 at 11:45
-
2If 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). Commented Dec 11, 2015 at 14:19
-
1Nice approach but this will add lots of null data, So you are saying that this way is still better than Join costs, right?– farid_92Commented Dec 11, 2015 at 21:13