patternsqlMinor
IS-A relationship with only single sub entity
Viewed 0 times
withsubsingleonlyentityrelationship
Problem
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.
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.
Solution
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:
rather than:
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
In this case it just makes sense to do:
SELECT * FROM words where isverb = 1rather than:
SELECT *
FROM words w
INNER JOIN verbs v ON w.type = v.id
WHERE w.type = 2The 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
Code Snippets
SELECT * FROM words where isverb = 1SELECT *
FROM words w
INNER JOIN verbs v ON w.type = v.id
WHERE w.type = 2Context
StackExchange Database Administrators Q#123230, answer score: 3
Revisions (0)
No revisions yet.