1

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?

3 Answers 3

2

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
4
  • 1
    The three table approach seems a bit dangerous -- if you have one example sentence that pertains to 10 words, and you change the sentence so that it effectively only applies to 8 now instead of 10, you have to remove the two broken associations. A two table approach forces you to keep your sentence tied directly to one word. I suppose it depends entirely on the nature of the application and any storage space requirements, but it's just a thought.
    – Cory
    Jan 10, 2013 at 23:25
  • @Cory: I see your point. However, I think that I can handle such danger with three table approach by checking the existence of the word in the modified example sentence and add/remove the associated words to.
    – Rasoul
    Jan 10, 2013 at 23:35
  • @Rasoul: I just wanted to make it a point, that's all! I'm sure you will find a way to maintain valid associations :)
    – Cory
    Jan 10, 2013 at 23:36
  • @Cory: Good point. I hadn't considered that. As Rasoul mentioned, if you knwo about it you can code around it, but it's certainly something to keep in mind
    – Greg
    Jan 10, 2013 at 23:55
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'
2

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.

2
  • 1
    The three table approach seems a bit dangerous -- if you have one example sentence that pertains to 10 words, and you change the sentence so that it effectively only applies to 8 now instead of 10, you have to remove the two broken associations. A two table approach forces you to keep your sentence tied directly to one word. I suppose it depends entirely on the nature of the application and any storage space requirements, but it's just a thought.
    – Cory
    Jan 10, 2013 at 23:10
  • @Cory I agree and I thought of that and you are right, it's a dangerous design if not handled with care. I will remove the suggestion.
    – Icarus
    Jan 10, 2013 at 23:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.