Of lookup tables...

[Mart]

OK... so we're setting up a DB of childrens books for a client.
Amongst the fields needed, the original idea was to have eight separate
fields each with a lookup table of about fifteen keywords describing the book.
Now... the client has found that there are cases where he would like more
than one option to be selectable from any chosen lookup table field.
I'm a bit of a novice at these things, but how does one get around this,
short of somehow trying to make sure that each lookup table can only possibly
have one option selected (which I don't think is realistic).

TIA

Mart

 

[Allen Browne]

If one book can have many keywords associated with it, you need a related
table to hold the keywords for the book.

I take it there are fixed keywords you are supposed to use, so you have a
table of keywords. The word itself can be the primary key, so the Keyword
table will have this field:
KeywordID Text (24) primary key

You already have a Book table, with fields such as:
BookID AutoNumber primary key
Title text title of the book
...

The new table might be named BookKeyword, and will have fields:
BookID Number relates to Book.BookID
KeywordID Text (24) relates to Keyword.KeywordID
The combination of the 2 will be the primary key.

The interface will be a main form bound to the Book table, with a subform
bound to the BookKeyword table. You can enter as many rows as you need in
the subform. On each row, you select a keyword from a combo box that has the
Keyword table as its RowSource.

Finally, you want to be able to filter the main form to only books that have
a particular keyword. For details of how to do that, see:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

 

[Mart]

Hey Allen, thanks!
I think this is getting a little beyond my grasp, but I'll ask a few more
(hopefully) pertinant questions:

1. You seem to be suggesting one table only with all keywords. Correct?
2. If this is the case, how can the user select more than just one keyword?
(This question probably shows up my dire lack of DB design knowledge )
3. At present there are five tables in this DB; tblbooks,
tblauthorsillustrators, tblauthordetails, tblillustratordetails,
tblbookdetails. The 8 fields with the various sub-categories of 15 keywords
are included in tblbookdetails. (Related to the above points), it seems I
should take these fields out and make a different table for them.
4. Could you just run the relationships by me again - these really DO
confuse me

Thanks again for you helpful response.

Mart

 

[Allen Browne]

Re 1: Yes: a table of the keywords you can use.

Re 2: You select as many keywords as you need, one per row in the *subform*.
The subform is bound to a table that has a many-to-one relation with your
tblBooks, so one book can have many related *records* (each one contains a
keyword.)

Re 3: I don't know what you have in tblBookDetails. Perhaps you have already
set this up with *one* field where you can enter a keyword, and you can
create many *records* related to a book in tblBooks.

Re 4: You have a many-to-many relation between books and keywords: one book
can have many keywords, and one keyword can apply to many books. You add
another table in the middle that resolves the many-to-many into a pair of
one-to-many. It's easiest to explain with examples. Here's another one:
http://allenbrowne.com/casu-06.html

 

[Mart]

Thanks Allen. Very helpful. I'll soldier on.

Re 1: Yes: a table of the keywords you can use.

Re 2: You select as many keywords as you need, one per row in the *subform*.
The subform is bound to a table that has a many-to-one relation with your
tblBooks, so one book can have many related *records* (each one contains a
keyword.)

Re 3: I don't know what you have in tblBookDetails. Perhaps you have already
set this up with *one* field where you can enter a keyword, and you can
create many *records* related to a book in tblBooks.

Re 4: You have a many-to-many relation between books and keywords: one book
can have many keywords, and one keyword can apply to many books. You add
another table in the middle that resolves the many-to-many into a pair of
one-to-many. It's easiest to explain with examples. Here's another one:
http://allenbrowne.com/casu-06.html