Table relationships, what am I missing?

D

danny.eve

Hello. I'm hoping I can get some help with the problems I'm having
with the basic logic of the table relationships in my Access database.

I'm in the process of making a database of words in a foreign language
in order to help me study for a forthcoming series of examinations. I
know that when experienced users read this they'll immediately see
where my thinking is breaking down but I've been wrestling with the
database design for a couple of weeks and I can't take too much time
off from the studying to complete the database, so I'd really
appreciate some help.

The reason d'etre for the Access database is that I want to be able to
display a word in the foreign language in a form, along with the
English translation and - crucially - my own cross references to other
words in the same list of words. The cross references come in a number
of different varieties.

These varities are as follows (with examples in English to illustrate
what I mean):

SameMeaning - being words with the same meaning. An example for the
word "speed" would be "rapidity".
SameSound - being words with the same pronunciation but different
meaning. An example for the word "sun" would be "son".
OppositeMeaning - being words with the opposite meaning. An example
for the word "cold" would be "hot".
Usage - being a word that combines with the word to create a phrase.
An example for the word "lie" would be "to tell a".
Distinguish - being words that are important to distinguish from
another due to common mistakes. An example for the word "your" would
be "you're".
TransIntrans - being transative and intransative versions of the same
verb. An example for the word "to tell" would be "to be told".
Misc - being a word which is somehow linked but whose relationship
does not fit into any of the above catagories. An example for the word
"politician" would be "politics".


And here is a more concrete example of actual data in the foreign
language from my database:

Word ID:682
Original Word: hankou
English translation: opposition, resistance
SameMeaning: tairitsu, teikou
SameSound: hankou
Distinguish: hanko
Misc: hankouki

As you can see, there can be more than one SameMeaning (or any other
of the cross references) and none of the cross references are
required. All of the cross referenced words also exist in the table,
for example tairitsu is there at Word ID 21, teikou at 66, etc. These
words have their own cross references (for example, "tairitsu" does
not need to be dintinguished from "hanko" so in its record the
Distinguish cross reference is different).

Anyone still following?

At first I went for the totally amateurish method of making a table
with endless SameMeaning1, SameMeaning2, SameMeaning3, etc. fields,
which would have been filled with the WordID of the word with the same
meaning. I quickly found this was a waste of effort.

Now I'm at the point where I have one table with the Word ID, Original
Word and English translation, then a table each for the SameMeaning,
SameSound, OppositeMeaning, etc. cross-references each with a dual
primary key of the WordID field and the cross reference. E.g.
tblSameMeaning has WordID and SameMeaning fields.

These individual cross-reference tables obviously need a relationship
so that, for the example of "hankou" above, when I open my main form
to Record 682 the Original Word (hankou) and the English translation
(opposition, resistance) are displayed; the cross references are
looked up then displayed (not in number form, but in the Original Word
form (i.e. tairitsu, teikou, etc. not their Word IDs 21 and 66).
HOWEVER, this is where my logic breaks down. These cross-references
should be looked up from the same table of words and translations that
"hankou" is coming from......anyone catch my drift?

Instead of a standard Products/Customers/Orders model of tables with
cross references to each other, in my case the cross references are
all internal ones to different records in the same table of data.

Now, this is the point where I've been tearing my hair out for a week.
I'm starting to think that Access may not even be appropriate for this
task. I've thought about linking the main words table to the cross
reference tables to a new table only containing a copy of just the
Word ID and Original Word fields, but this would be duplicating the
data and surely terrible database design. That or just giving up on
the database idea, but surely this kind of case where the cross
references would be repeated a lot in an Excel database is the area
where relational databases excel?


Anyway, I really hope someone hears my pleas! If any clarification is
needed just ask.

I'm using XP and Access 2000.

Thanks in advance.
 
K

Ken Snell \(MVP\)

I'd use these tables:

tblWord
WordID (primary key)
WordWord (contains the actual word)

tblRelateTypes
RelateTypeID (primary key)
RelateTypeDesc (contains the type of relationship -- e.g., SameMeaning,
SameSound, OppositeMeaning, EnglishTranslation, etc.)

tblWordRelates
WordRelatesID (primary key)
OrigWordID (foreign key to tblWord -- this is value of the original
word to which other words are related)
RelateWordID (foreign key to tblWord -- this is value of the word
that is the related word via the relatetype)
RelateTypeID (foreign key to tblRelateTypes -- this is value of the
type of relationship)


Then just put all possible words in tblWords, and use tblWordRelates to
relate them one to another, identifying the type of relationship for each
word combination).
 
S

Steve

Here's a thought after quickly reading your post ---

TblWord
WordID
Word
EnglishTranslation

TblSameMeaning
SameMeaningID
WordID
WordWithSameMeaning

TblSameSound
SameSoundID
WordID
WordWithSameSound

TblOppositeMeaning
OppositeMeaningID
WordID
WordWithOppositeMeaning

TblUsage
UsageID
WordID
UsagePhrase

TblDistinguishFrom
DistinguishFromID
WordID
DistinguishFromWord

TblTransIntrans
TransIntransID
WordID
TransitiveVersion
InTransitiveVersion

TblMisc
MiscID
WordID
MiscWord

Create a form based on TblWord to display the word and its English
translation. Add an unbound option group with seven options for Same Meaning
through Misc. Create seven forms to be used as subforms based on
TblSameMeaning through TblMisc. Create a subform control on the Word form
and set the Link Master and Link Child fields to WordID. Leave the Source
Object property Blank. Now in the Afterupdate event of the option group,
write a Select Case procedure with Cases 1 to 7 and for each Case set the
Source Object property of the subform control to the appropriate subform.

Your main form will be able to display a selected word and its English
translation. You will be able to select from 1 of the seven options and the
subform will display the associated information for the option you selected
for the word in the main form.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
 
S

StopThisAdvertising

Steve said:
Here's a thought after quickly reading your post ---

TblWord
WordID
Word
EnglishTranslation

TblSameMeaning
SameMeaningID
WordID
WordWithSameMeaning

TblSameSound
SameSoundID
WordID
WordWithSameSound

TblOppositeMeaning
OppositeMeaningID
WordID
WordWithOppositeMeaning

TblUsage
UsageID
WordID
UsagePhrase

TblDistinguishFrom
DistinguishFromID
WordID
DistinguishFromWord

TblTransIntrans
TransIntransID
WordID
TransitiveVersion
InTransitiveVersion

TblMisc
MiscID
WordID
MiscWord

Create a form based on TblWord to display the word and its English
translation. Add an unbound option group with seven options for Same Meaning
through Misc. Create seven forms to be used as subforms based on
TblSameMeaning through TblMisc. Create a subform control on the Word form
and set the Link Master and Link Child fields to WordID. Leave the Source
Object property Blank. Now in the Afterupdate event of the option group,
write a Select Case procedure with Cases 1 to 7 and for each Case set the
Source Object property of the subform control to the appropriate subform.

Your main form will be able to display a selected word and its English
translation. You will be able to select from 1 of the seven options and the
subform will display the associated information for the option you selected
for the word in the main form.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

--
You are *not* a resource at all !!
Tell us you will stop advertising here, or get lost for another year or so...
http://home.tiscali.nl/arracom/whoissteve.html

ArnoR
 
D

danny.eve

Ken,

Thanks for your swift reply. As soon as I cast my eyes over it I saw
how much sense it made. I'm now having a bit of trouble with
displaying the data as I wish; however I'll try to solve those
problems myself and post in the relevant newsgroup if and when I reach
any insurmountable problems.

However, I'd just like to confirm a couple of things about the advice
you gave me.

Firstly, with the term "foreign key", I'm assuming that this is a the
definition of a 1-to-many relationship with the WordID, and as such
joining the tables on the Relationships window will suffice to create
this link.

Secondly, when I attempted to join the OrigWordID and RelateWordID
from the tblWordRelates to the WordID field of tblWord as you
described below in the Relationships window a dialog informed me that
this was not possible and a second instance of tblWord (tblWord_1) was
created. Is this normal?

Many thanks again.
 
K

Ken Snell \(MVP\)

Answers inline...

--

Ken Snell
<MS ACCESS MVP>


Ken,

Thanks for your swift reply. As soon as I cast my eyes over it I saw
how much sense it made. I'm now having a bit of trouble with
displaying the data as I wish; however I'll try to solve those
problems myself and post in the relevant newsgroup if and when I reach
any insurmountable problems.

However, I'd just like to confirm a couple of things about the advice
you gave me.

Firstly, with the term "foreign key", I'm assuming that this is a the
definition of a 1-to-many relationship with the WordID, and as such
joining the tables on the Relationships window will suffice to create
this link.
Yes.




Secondly, when I attempted to join the OrigWordID and RelateWordID
from the tblWordRelates to the WordID field of tblWord as you
described below in the Relationships window a dialog informed me that
this was not possible and a second instance of tblWord (tblWord_1) was
created. Is this normal?
Yes.




Many thanks again.

You're very welcome.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top