HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

IS-A relationship with only single sub entity

Submitted by: @import:stackexchange-dba··
0
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.

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:

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

Code Snippets

SELECT * FROM words where isverb = 1
SELECT * 
FROM words w 
INNER JOIN verbs v ON w.type = v.id 
WHERE w.type = 2

Context

StackExchange Database Administrators Q#123230, answer score: 3

Revisions (0)

No revisions yet.