Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

migrated from stackoverflow.com Jan 11 '13 at 7:39

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 2 down vote accepted

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
share|improve this answer
    
This is a nice idea. Thank you! –  Rasoul Jan 10 '13 at 23:11
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 '13 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 '13 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 '13 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 '13 at 23:55
add comment

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'
share|improve this answer
add comment

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.

share|improve this answer
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 '13 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 '13 at 23:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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