Relational Structure - Beginner Question

[CrazyFileMaker]

Hello,
I'm trying to store a dictionary in a database.

I am a newbie access user, and my questions on this are very basic.

Firstly: What is the ideal relational structure for such a database?

Here's what I'm thinking:

I make a parent table "WordList" with field "Word", and each record in that field contains a word from the dictionary.

I then create a separate table for each word in the dictionary, and each word's table would store the properties of that word under different fields (i.e. pronunciation, definition, part of speech, synonyms, antonyms, etc.)

In this scenario, what would the relationship be from the words in my parent table "WordList" to each individual word's table? Is it One-to-One? One-to-Many?




Secondly: How would you suggest setting up the keys for such a table?
If each word in the "WordList" table is a primary key, then how would you suggest I store the foreign key in each individual word's table? Do I have to make a field named "Word" in each individual word's table and make it the foreign key?

Thirdly: Along the lines of my last question, is it possible to make the foreign key in a table the value of a field, and not the field itself? In other words, does a foreign key have to be a field name, like say, "PartOfSpeech"?, or can it be a record's value under that field, like say, "noun"?

Sorry about the newb questions. I'm somewhat competent at excel and programming in VBA, but I'm still trying to wrap my head around databases and their structure.

Thanks for any and all help! All suggestions and corrections are much appreciated!

[ajetrumpet]

What is the ideal relational structure for such a database?

that's absolutely impossible to answer, and every expert will say the same thing. every situation will vary the result.

I make a parent table "WordList" with field "Word", and each record in that field contains a word from the dictionary.

that's a BIG table.

I then create a separate table for each word in the dictionary

NO. NEVER. and it is probably impossible anyway, as it will certainly overload the memory capability, or structure capacity of Access.

and each word's table would store the properties of that word under different fields (i.e. pronunciation, definition, part of speech, synonyms, antonyms, etc.)

relationally speaking, yes, this is probably as close to correct as you could get.

In this scenario, what would the relationship be from the words in my parent table "WordList" to each individual word's table? Is it One-to-One? One-to-Many?

one to many

Secondly: How would you suggest setting up the keys for such a table?

keys, to me at least, are irrelevant. by default, always rely on autonumber for primary keys. they mean nothing, but they guarantee 0 risk of record duplication. If I'm accurate at all, I'm sure that is agreed upon by most access developers.

If each word in the "WordList" table is a primary key

No. PK is associated with a field, not records, but i think that's what you mean.

then how would you suggest I store the foreign key in each individual word's table?

googling 'one to many relationship' will give you the answer in one short paragraph I'm sure. the FK is generally a duplication of the PK (same value), wherever that pk happens to be.

Do I have to make a field named "Word" in each individual word's table and make it the foreign key?

yes you would have to, but this is impractical, as I already said.

Thirdly: Along the lines of my last question, is it possible to make the foreign key in a table the value of a field, and not the field itself?

already explained above. google ''ms access primary key'' if you are still unsure of what a PK is and its purpose.

Sorry about the newb questions. I'm somewhat competent at excel and programming in VBA

they are actually newbie questions, but so what, right? Personally, the biggest hurdle I gathered from reading this post is that you seem to be hung up on the PK/FK concept. But it does seem like you are knowledgable about vba entities and their properties. PK/FK's ARE actually properties themselves. Hence, if you want to assign a PK to a database table's field in access via code, you write something like:

index.primary = true

and that's verbatim from part of the vba help menu.

so think of the PK as a property or an attribute. basically, they're the same things, but point to different object of course. a PK, in dictionary language, is basically an adjective. it describes the field mostly, and influences its behavior a little. Hardly at all, but still does a little.

HTH!

[CrazyFileMaker]

Thanks for the feedback.

I appreciate you taking the time to address all my questions. You've helped me get clarity on some issues I was clearly hazy on.

Cheers!