I’m not sure if this is the right place to ask this question, but anyway. I’m creating my first yii powered website, which is an online dictionary. For this purpose I have to use three different dictionaries: everyday terms, chemical terms, computer terms. All of these three dictionaries have the same structure:
id term definition
.
Now the question is 1) should I use three different tables, one table for each dictionary? or 2) should I use one table for all dictionaries, but add one extra column “tag” and tag all terms depending on it’s dictionary? or 3) I can add extra columns, and merge all tables depending on the same term, so it’ll look like:
id term definition_dic_1 definition_dic_2 definition_dic_3
I want to see my search result as follows:
Term
Dictionary 1
definition
Dictionary 2
definition
Dictionary 3
definition
Which way will be faster? Why way will be easier to implement using yii?
Sorry for this question, but really need someone’s advice.
If you are going to have three definitions for most of your terms, and you don’t think you’ll be adding more contexts then you could just use a single table:
termId
term
definitionMedical
definitionEveryday
definitionComputer
This will be the fastest, and probably easiest to set up.
A little more advanced would be to build in some flexibility for the future, you might add another dictionary.
Two tables:
Table - Terms
termId
term
Table - Definitions
termId
dictionary (can name them eg. MEDICAL, or for greater search speed use numbers).
definition
That way, if you add another dictionary, you will not need to change your database structure. You can also search for a definition across all dictionaries by doing this:
SELECT * FROM Definitions INNER JOIN Terms USING(termId) WHERE definition LIKE '%$search%'
If you build your database the first way you will have to do:
SELECT * FROM Definitions WHERE definitionMedical LIKE '%$search%' OR definitionEveryday LIKE '%$search%' OR definitionComputer LIKE '%$search%'
