Need help in designing Dictionary DB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Need help in designing Dictionary DB

Author

Topic

Glyc07
Starting Member

4 Posts

Posted - 2009-09-06 : 07:39:14

Hi there

I'd like to create a dictionary website and right now I'm facing with database design. It's my first, more advanced, so I'm not sure if it is optimal

I have "Entries" table, where I store all information about dictionary entry -> there are entries in both language (english and polish right now)

another table I've got is "EntriesConnection" -> many to many relation to connect entries with their translations

"Categories" -> used when displaying translations (translations are sorted by CategoryID element and appropriately displayed )

"Synonyms" -> many to many relation to connect entries with theri synonyms

and last one is "Samples" -> to show usage of translation in opposite language than searched world

now something about SQL query

when sbd enter desirable world to search box first thing I do is search the "Entries" table to find matching Entry.Name, when I find it i use Entry.EntryID to gather all related information:


SELECT                                entry.Name,
                                            entry.Audio,
                                            entry.CategoryID,
                                            entry.Type,  
                                            entry.Language,
                                            translation.EntryID AS translationEntryID, 
                                            translation.Name AS translationName, 
                                            translation.CategoryID AS translationCategoryID, 
                                            translation.Type AS translationType, 
                      
                                            synonym.EntryID AS synonymEntryID, 
                                            synonym.Name AS synonymName, 
                      
                                            sample.SampleID AS sampleID,
                                            sample.Body AS sampleBody
FROM                                   dbo.Entries AS entry 
LEFT OUTER JOIN                dbo.EntriesConnections AS entriesConnection ON entriesConnection.EntryID = entry.EntryID OR 
                                                                                                                              entriesConnection.SecondEntryID = entry.EntryID 
LEFT OUTER JOIN                dbo.Entries AS translation ON entriesConnection.SecondEntryID = translation.EntryID AND entriesConnection.SecondEntryID <> entry.EntryID OR 
                                                                                              entriesConnection.EntryID = translation.EntryID AND entriesConnection.EntryID <> entry.EntryID 

LEFT OUTER JOIN                dbo.Synonyms AS synonyms ON synonyms.EntryID = translation.EntryID OR synonyms.SecondEntryID = translation.EntryID 
LEFT OUTER JOIN                dbo.Entries AS synonym ON synonym.EntryID = synonyms.EntryID AND synonyms.EntryID <> translation.EntryID OR 
                                                                                           synonym.EntryID = synonyms.SecondEntryID AND synonyms.SecondEntryID <> translation.EntryID 

LEFT OUTER JOIN                dbo.Samples AS sample ON sample.EntryID = translation.EntryID
WHERE     (entry.EntryID = @EntryID)

this is how I query my DB right now, as I wrote above it's my first more advanced db and I'm not sure if this design and query are the best options, so if some1 could provide me a little piece of advice I'd be appreciative!

Thanks a ton!,

Glyc07

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources