Of lookup tables...

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Jamie Collins]

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

In our shop, when we're designing user interfaces we often ask, "What
would Outlook do?"

We don't model books but if we did we'd probably ask, "What would
Amazon do?"

Consider this URL

http://www.amazon.com/Celkos-Programming-Kaufmann-Management-Systems/dp/0120887975/

Having a contraction of author-title-publisher makes the data more
readable but that's not the key being used here (it wouldn't be able
to differentiate between editions, for example). Rather, the key is
0120887975 which, if you look towards the end of the page, is the ISBN
number.

ISBN is a great key in many ways (intelligent, stable, trusted source,
check digit, fixed length, familiar, etc). Not only does it exist in
just about everyone else's database, it is readily available in the
reality being modelled (hint: look at the barcode on the book's back
cover).

I admit that finding a *usable* natural key is not always easy (person
identifiers are a particular pain) but it doesn't come much easier
than the no-brainer ISBN for books.

Jamie.

--

 

[Jamie Collins]

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

We don't model books but if we did we'd probably ask, "What would
Amazon do?" [snipped]

Shorter answer:

1) Navigate http://www.amazon.com/Celkos-Programming-Kaufmann-Management-Systems/dp/0120887975/
2) Buy the book.
3) Read PP54-62: 3.13.3 "Look for the Properties of a Good Key" ;-)

Jamie.

--