I'm lost at your requirements.
What kind of data do you have exactly, and what are the relationships between them?
You have words, and you have categories. How are they related?
How many categories can one word have?
How many words can one category have?
Is there anything else?
A single table works great if
- There is only one category for each word
- There is only one word for each category
- You will not rename categories later
Otherwise we're going to have get dirty.
Originally Posted by Adak
The entries are all words.
one example is:
Category= shoe
Related words= shoe lace, insole and ... any other words/expression related to the word shoe
and really I cant come up with any exact number
I guess there are situations in which the criteria isn't met
Thank you so much for the help,
I am reading an article about foreign keys
We are only really interested in one or many (i.e. more than one).
From what I can see in your data, you have words, categories and related words.
When designing a database, one typically separates entities and storage. Entities are description of all your data and storage is a way to store the data in your database.
So basically entities would be categories and words. Storage would be a table that stores information about what categor(y/ies) a word belongs to and related words or what have you.
Each entity is usually a table. This is often called a lookup table. It is typically used to reduce the possible selections for a column to a set of predefined values.
So you might make a table for Words and for Categories. This is not necessary, but it can help in certain situations. Look for what lookup tables can do for you.
The storage table would probably look something like
Word Category RelatedWords
At least, from what I gather from what you've said so far.
However, if we consider that one word may have many related words, we get duplication, and that is usually when we split the table into a so-called link table.
If one word has multiple categories, we do the same for that. A link table.
Assuming one word, one category and one word, multiple related words, this is one possible structure of the database:
Table: WordsTbl (Storage table)
Fields: Word (Number, Primary key), Category (Number)
Table: WordsLTbl (Lookup table)
Fields: ID (Number, Foreign key/Primary key), Val (String)
Table: CategoryLTbl (Lookup table)
Fields: ID (Number, Foreign key/Primary key), Val (String)
Table: RelatedWordsLnkTbl (Link table)
Fields: Word (Number), RelatedWord (Number)
Keys: Word and RelatedWord makes up the primary key/foreign key (yes, it is valid to have multiple columns for a key)
Then for relations:
WordsTbl.Word <-- (1:1) --> WordsLTbl.ID. Use WordsLTbl.Val for lookup (i.e. what the user will see in the dropdown list in access).
WordsTbl.Category <-- (1:1) --> CategoryLTbl.ID. Use CategoryLTbl.Val for lookup (i.e. what the user will see in the dropdown list in access).
WordsLTbl.Word <-- (1:Many) --> RelatedWordsLnkTbl.Word. Use WordsLTbl.Val for lookup (i.e. what the user will see in the dropdown list in access).
WordsLTbl.Word <-- (1:Many) --> RelatedWordsLnkTbl.RelatedWord. Use WordsLTbl.Val for lookup (i.e. what the user will see in the dropdown list in access).
Each row in RelatedWordsLnkTbl contains a related word for a word. Several rows with the same word are used to indicate that one word have several related words. E.g.:
"Word A", "Related Word A"
"Word A", "Related Word B"
"Word B", "Related Word C"
etc
A primary key and foreign need not be numbers. They can be any indexable data type (e.g. strings). However, if you later wish to change the spelling of a word or a category, it's much easier to use a unique identifier for them instead of directly putting in the string. If you put in the string, then if you change an entry in WordsLTbl, for example, then you need to update WordsTbl with the new word, or the references won't work anymore! Sure, Access can do this for you, but as your database grows larger, this can become a bottleneck. That is why I always use unique identifiers.
I hope this is enough to get you started. There is a lot of material you need to review in order to understand this.
