Database structure

Hi guys!

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:


Dictionary 1


Dictionary 2


Dictionary 3


Which way will be faster? Why way will be easier to implement using yii?

Sorry for this question, but really need someone’s advice.

Thank you!


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:






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



Table - Definitions


dictionary (can name them eg. MEDICAL, or for greater search speed use numbers).


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%'

Thank you! I think the second option would the best in my case :slight_smile: