keywords for each record

[w_r_fox]

Each record in my database refers to an article from a journal of history. I
can be provided with a list of keywords describing the contents of each
article, by which users might wish to search for articles. The keywords fit
into say 5 categories - dates, places, names etc.- and the total number of
keywords is large in each category.

Can anyone suggest how to set up the keywords so that users can search?
First, how to input the keywords in the articles table; then how to manage
the basic list of all keywords in a category, then how to search.

Even if you could recommend a relevant book this would be useful guidance.

 

[John Nurick]

Hi,

You have a many-to-many relationship: each article can have many
keywords and each keyword can apply to many articles. The general idea
is to create a table of keywords (one record per keyword) and a "joining
table" in which each record links a given keyword to a given article.

But there are a few conceptual or design details that need to be sorted
out in order to get a suitable data structure.

What happens if the same keyword appears in more than one category (e.g.
"Washington" could appear as both a place and a name)? Do you want your
users to be able to distinguish in their searches between the two?

 

[w_r_fox]

Thanks for getting me started. Following your suggestion, I try to make a
"joining table", I presume from a query which contains the articles table
and the keywords tables. Let's deal with a single keyword table, say
"keyword_names". What field(s) from that table should go in the query? There
are many fields, each one a different keyword - Smith, Jones, ... . Surely
they shouldn't all go in, with the same criteria in each one? That can't be
right.

Sorry if I've misunderstood. Please can you clarify?

 

[John Nurick]

You'll have to provide more information, because I have no idea of the
structure of your existing data. Without knowing that, and without
answers to the questions I asked before, I'm guessing.

Leaving aside the question of categories of keywords, the general idea
would usually be

tblArticles
ArticleID (primary key)
Title
other fields

tblKeywords
Keyword (primary key)
possibly other fields (e.g. an explanation of the keyword's use)

tblArticleKeywords
ArticleID (foreign key into tblArticles)
Keyword (foreign key into tblKeywords)
(both fields in primary key)

If at present you have multiple keyword fields in the Articles table,
you need to run a series of append queries to get all the different
keywords into tblKeywords (which serves as a list of all approved
keywords to support referential integrity and avoid mis-spellings). If
the keyword fields may contain lists of keywords, the queries need to
split each keyword out of the list.

Then you need another series of append queries that will create a record
in tblArticleKeywords for every keyword associated with every article.

Thanks for getting me started. Following your suggestion, I try to make a
"joining table", I presume from a query which contains the articles table
and the keywords tables. Let's deal with a single keyword table, say
"keyword_names". What field(s) from that table should go in the query? There
are many fields, each one a different keyword - Smith, Jones, ... . Surely
they shouldn't all go in, with the same criteria in each one? That can't be
right.

Sorry if I've misunderstood. Please can you clarify?