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.
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.