Efficient SQL database design for recording examples of a word

Question

I am designing a dictionary-like program which stores (and displays) a few example sentences of the given word. One way to design a SQL database for such problem is to create a table with two columns word and example and then for each example sentence add a new row with the repeated word in the column word. Is this approach common and efficient? I was also thinking of dynamically creation of a table of examples for each word and then relate the table to the word somehow. Is this approach better than previous one?

Answer 1

You could go 1 step further from the other suggestions and take the example sentance into a new table as well. As a sentance is made up of many words the same sentance could be used for different words.

Word
=======
Word_Id INT Identity(1,1) PRIMARY KEY,
Word nvarchar(50) not null

ExampleSentance
========
Sentance_Id INT Identity(1,1) PRIMARY KEY,
Senatnce nvarchar(MAX) not null

Word_ExampleSentance
========
Word_Id INT PRIMARY KEY,
Sentance_Id INT PRIMARY KEY

When selecting a word and all the sample sentances you just need to join the tables:

SELECT *
FROM   Word
JOIN   Word_ExampleSentance 
  ON   Word.Word_Id = Word_ExampleSentance.Word_Id
JOIN   ExampleSentance 
  ON   ExampleSentance.Sentance_Id = Word_ExampleSentance.Sentance_Id

Answer 2

It sounds like you would need two tables:

Word
=====
Id     (primary key, int, identity)
Word   (a string type, not null, unique)

And

WordExample
===========
Id       (primary key, int, identity) (optional, not used in this example)
WordId   (foreign key to Word.Id)
Sentence (a string type, not null)

(or however you'd like to appropriately name them).

This approach prevents storing each word multiple times in the same table. To query for all examples for a word, you add a JOIN to your query:

SELECT
    w.[Word], we.[Sentence]
FROM
    [Word] w
LEFT JOIN
    [WordExample] we ON w.[Id] = we.[WordId]
WHERE
    w.[Word] = 'abc'

Answer 3

I would have 2 tables: Word: word_id, word

Then Example: word_id,example_sentence

This will likely be more efficient in terms of storage since you only need 4 bytes to store an id and possibly many more to store duplicates of a word.