Structuring a dictionary's database

[alcaeus]

Hello all!

 

I am wondering how the average dictionary's database would be structured. (e.g. the database for http://dictionary.reference.com/ )

 

Some words may have up to twenty definitions, whereas some may on have one. How is this usually addressed? Should I just make twenty columns called "definition 1", "definition 2", etc. and then leave the majority blank for most words? Or is this handled in a completely different way?

 

Any advice would be greatly appreciated! 

[The Little Guy]

make 2 tables

 

table 1: dictionary

  - dictionary_id

  - word

 

table 2: definition

    - definition_id

    - dictionary_id

    - definition

 

so in table 1 dictionary_id would be an auto_increment filed, but not it table 2 and you would match on those two columns, it would look like so:

 

select * from dictionary dic left join definition def using(dictionary_id) where dic.word = "bat";

 

you would then get this:

 

+---------------+------+---------------+---------------+----------------------------------------+
| dictionary_id | word | definition_id | dictionary_id | definition                             |
+---------------+------+---------------+---------------+----------------------------------------+
| 1             | bat  | 1             | 1             | an animal with wings                   |
| 1             | bat  | 2             | 1             | an instrument used by baseball players |
+---------------+------+---------------+---------------+----------------------------------------+

[alcaeus]

Thank you so much! This had been bugging me for ages. Now that you've said it, it seems so obvious!