Database Management
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesDatabase Management

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old February 10th, 2004, 05:10 PM
saodl saodl is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Feb 2004
Location: Los Angeles, CA
Posts: 21 saodl User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Question design problem: relationship convergence

SUMMARY:
I have many similar tables that should all be related to each other through many to many relationships, and I am not sure what the best way to do this is.
I am currently considering storing all the relationships in a central 'convergence' table. I am not certain this is the best way. Any advice would be appreciated.

DETAILS:

I am working on a database for a multi-lingual bible study tool. In order for the cross-language implementation to work, words in the English (and other - Chinese at the moment) need to be related to the greek (or hebrew) word(s) they from which they are translated, as well as to each other. This lets me produce an 'interlinear' form:

ie (using greek transliterations
en: me and you
gk: ego kai su

For this reason (and in order to link them to definitions) words are stored individually in the database. At the moment, because it is cleaner and because I may add more languages/versions later, I am keeping each version/language in it's own table.

Conceptually most words in every version should connect to 1 or more words in any other version. For two tables this is no problem and I use a standard many to many construct:

english: enID*, word
greek: gkID*, word
en_gk_relation: {enID(fk), gkID(fk)}*

However when I start getting more tables, it becomes more complex:
  • I could limit myself to defining in advance which tables can be related, but that is counter to the way this tool is meant to be used.
  • I could let english be related to chinese Through the greek table (en <*-*> gk <*-*> ch), but the Chinese and English may be similar to each other in ways they are not similar to the Greek.
  • I am currently considering using a centralized relation table of the form {enID(fk), gkID(fk), chID(fk)...}*. This seems like it could work but it also seems unwieldy.
  • Other options...?

One final problem I have is that sometimes there is simply no match for a greek word in the english (or vice-versa). In the reference table this effectively produces a NULL value, which violates the primary key. I can circumvent the error (put a 0), but that's not really any better from a functional standpoint.

In these cases, I would still like to include the missing word in as near the right order as possible (after the previous greek word, or before the next, according to the greek order). However with a NULL or a substitute value, it is rather difficult to get any closer than inside the right verse...

Thanks for your help and opinions.

siri

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesDatabase Management > design problem: relationship convergence


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 2 hosted by Hostway
Stay green...Green IT