Basic design and relationship queries

[Mart]

I've been battling for a few days trying to understand how best to design a
DB for a collection of children's books, with associated authors and
illustrators. I've received much help (thanks Tina and others), but I'm
clearly not even getting the basics. So...

I have set up 4 tables, as there are four main streams of information,
namely tblBooks, tblAuthors, tblIllustrators and tblKeywords. But...

1. I don't have a clue as to how the relationships should be established
between any of the tables, especially how the tblKeywords (which is a list of
words that may apply to the content of any book, and of which there may of
course be more than one choice per book) relates/links with the tblBooks, so
that the user can select any number of keywords to narrow down the number of
books returned.
2. There is clearly a many-to-many relationship between tblBooks and all the
other tables. As a start I have tried (and failed) to set up a third junction
table between tblBooks and tblAuthors, but I just can't do it. Where I get
stuck is how to actually get the Primary Keys from the two main tables into
the junction table. As an example, both Primary Keys of the two main tables
are ID's with the datatype set to Autonumber. But, when I make the junction
table I need to start it with one field, so presumably can't set its datatype
to autonumber (or what am I missing here?). Also, what other fields should go
into this junction table?

At this point I should say that no book's author is also it's illustrator
and vice-versa, but a book could have more than one author or illustrator.

Thanks for bearing with me

Mart

 

[Cheese_whiz]

Hi Mart,

When you have an autonumber as the pk in one table, and you want to use it
as the fk in another, you just use a number field in the second table. That
is, it's an 'exception' to using the exact same datatype for fields that you
use to relate one table to another.

So, you'd make your junction table with, got example, BookID as the first
field with datatype number (long integer I think is correct). Then your
second field would be keywordID with the datatype number (long integer).
Then highlight both fields in design view and set them both (or the
combination of the two) as the primary key for your new table. Then just
close (and in the process save) your new table as something like
tblBookKeyword.

Then go back to the relationships window, 'show' your new table (pressumably
you've already set the oroginal two tables to show). Then, choose the pk in
one of the original tables.....highlight the field, left-click/hold/drag the
field and drop it on it's corresponding field in the new/junction table. Do
the same for the other main table. Then just click each relationship line
and set the referential integrity settings you want (probably first two boxes
checked and third unchecked, but up to you).

That's all there is to it I think.

CW

 

[BoniM]

The most efficient set of relationships for your database would be a one to
many relationship from the author table to the book table (Where you only
identify the author by it's primary key field in the author table) and a one
to many relationship from the illustrator table to the book table (same).
You would have a one to many relationship from the book table to the keywords
table. (I have a similar database, tho I don't have a keywords table.)
A very oversimplified list of your basic fieldnames for these tables might
look something like this:

Author Table
AuthorID (One half of one to many relationship with Book Table)
Authors

Illustrator Table
IllustratorID (One half of one to many relationship with Book Table)
Illustrators

Book Table
BookID
Book
AuthorID (Many half of one to many relationship with Book Table)
IllustratorID (Many half of one to many relationship with Book Table)

Keywords Table
BookID
Keyword

I know that many children's books have more than one author (Bill Martin Jr.
& John Archambault, for example) but I identify both of them in a single
record for all books they wrote together. I may have their names in again
for any books they wrote alone or with other authors, but I have found this
to be the least complicated, easiest to search method...

Good Luck! I hope I didn't confuse the issue!

 

[Martin Stabrey]

Thanks CW. Exactly what I needed... a step-by-step walkthrough.
The first of no doubt many issues overcome!

Mart

 

[Martin Stabrey]

Thanks!
I actually understand most of what you're saying... hooray... it's like the
lights are beginning to be turned on!
But, I'm still confused as to how the keywords will "talk to" the books
table as it's not related to it.
In the same breath, with this method will a user be able to select more than
just one keyword when searching for a book?

As a side issue (trying to cover all eventualities), what happens when an
author is also an illustrator, or vice-versa?

Thanks again for the help. Really appreciated!

Mart

 

[Cheese_whiz]

Glad that helped, Martin.

CW

Thanks CW. Exactly what I needed... a step-by-step walkthrough.
The first of no doubt many issues overcome!

Mart

 

[Ken Sheridan]

Mart:

The model suggested by BoniM doesn't go quite far enough. What the Book
Table and Keywords Table represent in the model are many-to-many
relationships between the Books entity type and the Authors/Illustrators
entity types on the one hand, and the Books entity type and Keywords Entity
type on the other. So you also need tables to represent the Books and
KeyWords Entity types. The Book column (i.e. book title) should go in this
Books table not in the table modelling the relationship as at present.

The Book Table in the current model is not properly normalised as in the
event of a book having more than one author the book title would be repeated
for each, which leaves it open to errors as the same title could be entered
differently in each case (I once found three versions of my own name as
author of technical papers in a database I did some work on). Also if a book
has more than one author the illustrator would be repeated for each author,
again giving rise to the risk of update anomalies.

I'd suggest you use a single Authors table to include both writers and
illustrators, i.e. with one row for each person, with columns AuthorID,
FirstName, LastName etc. The Books table would have columns BookID and
BookTitle, along with other columns for attributes of the book such as its
ISBN.

The many-to-many relationship between Authors and Books would be modelled by
a table BookAuthors with columns BookID, AuthorID, AuthorType, the last
having possible values 'Writer' and 'Illustrator'. This table is now
normalized as each 'fact' is stored only . Its primary key is a composite
one made up of all three columns. This allows for a writer to also be the
illustrator of a book as they would be represented by two rows in
BookAuthors, with 'Writer' as the AuthorType value in one case, and
'Illustrator' as the AuthorType value in the other case. The relationship is
thus as follows, with the > and < characters indicating the 'many' side of
the relationships:

Books---<BookAuthors>---Authors

The model in respect of keywords follows a similar patter, with a Keywords
table. In this case as each keyword is unique the table only needs the one
column, Keyword, which is its primary key (a so-called 'natural' key as
distinct from a numeric 'surrogate' key). The many-to-many relationship
between Books and Keywords is modelled by a table BookKeywords say, with
columns BookID and KeyWord, which are the table's composite primary key. The
relationship is thus:

Books---<BookKeywords>---Keywords

Referential integrity should be enforced in all the above relationships.
This prevents values being entered in the referencing (many side) tables
which don't exist in the referenced (one side) tables. It also prevent rows
being deleted from the referenced tables if a matching row exists in a
referencing table.

Regarding your point about searching for books on the basis of multiple
keywords this could be done in a query using the IN operator, ….WHERE Keyword
IN("Dog", "Cat", "Mouse"). This would find books with any of these keywords.
Unfortunately you can't use a parameter with the IN operator, so to allow
the user to enter a list of keyword (or better still select them form a
multi-select list box) you have to use a bit of trickery, which you'll find
at:


http://support.microsoft.com/kb/100131/en-us


The above link only shows how to do it by means of a simple parameter in
which the list of keywords is typed in, not by means of selection from a
multiselect list box, but I can walk you through how to set that up if you
wish.

Ken Sheridan
Stafford, England

 

[JimaldoFratelli]

TBL_AUTHORS
*author_id
author_name

TBL_ILLUSTRATORS
*illustrator_id
illustrator_name

TBL_KEYWORDS
*keyword_id
keyword

TBL_BOOKS
*book_id
*author_id (1-1 relationship with TBL_AUTHORS)
*illustrator_id (1-1 relatinship with TBL_ILLUSTRATORS)
book_title
(adding the illustrator_id key assumes ONE author will only write ONE book
with the same name, but allows for a book being re-printed with a different
illustrator)

TBL_BOOK_KEYWORDS (many-many relationship with TBL_BOOKS)
*book_id
*keyword

create the above tables, create the reltionships as defined, then write a
query to create a view of all the tables. this should (hopefully) allow you
to write a query which will return a list of books/authors/illustrators based
on the "keywords" you enter in your query. i.e. "show all books where
KEYWORD ='whatever the keyword'

or "show all books where "keyword" inlist

i think that should do it...but then again, i'm a bit rusty!

Jimaldo

 

[Martin Stabrey]

Thank you all for being so helpful! But then again, the answers that people
give in this newsgroup always are quite excellent.

 

[Ken Sheridan]

Jimaldo:

I think there are a few problems with your TBL_BOOKS in this model.

Firstly the relationship of books to authors and illustrators is many to
many, so the table would need decomposing into one to model the Books entity
type and another to model the relationship type between it and
Authors/Illustrators. Even then there would be a problem because the latter
table would be trying to model two relationship types, books to authors and
books to illustrators.

I don't think that separate Illustrators and Authors tables are necessary in
fact. Writers ands Illustrators can both be considered authors (Fay Godwin
was vehemently insistent that she was an author not an illustrator of the
books containing her pictures, and insisted on being credited as such), so
you just need an AuthorType column in a single authors table with possible
values Writer or Illustrator. The single many-to-many relationship between
books and authors can then be modelled by a table without any problems.

If separate Authors and Illustrators tables are used this would be because
they are regarded as sub-types of some (super) type, so a table to model this
(super) type would be required, related one to one to authors and
illustrators, and the many to many relationship would be between this (super)
type and books, and thus easily modelled by a table.

Ken Sheridan
Stafford, England

 

[JimaldoFratelli]

Hi Ken, i think i understand what youy're saying, as i mentioned, i am a bit
rusty with this!

however, i was thinking that it may be possible that an author could
re-publish a book with a different illustrator, hence, a seperate table for
illustrators.

jimaldo

 

[JimaldoFratelli]

Hi Ken

as mentioned, i was taking into account that a book mey be re-published with
a different illustartor, hence the need for seperate tables

however, i neglected to take into account that Mart mentions, a book may
have more than one author and/or illustrator. that's what i get for not
reading the brief properly!!!

 

[Fred Boer]

Hello all:

And here's another wrinkle: Shelf Order. Suppose you have a multi-authored
book. Whose last name do you use on the spine label? Well, you have to
assign one of the authors as the "primary" author. So you need to assign not
only an authorship type (author, illustrator, editor, etc.), but also
indicate which of the multiple authors should be used to generate the spine
label..

So perhaps:

tblBookAuthor

BookAuthorID (Autonumber)
BookID
AuthorID
AuthorRankID
AuthorTypeID

tblAuthor

AuthorID(Autonumber)
AuthorFirstName
AuthorMidName
AuthorLastName
AuthorPenName
AuthorBirthYear
AuthorDeathYear
AuthorNote

tblAuthorType

AuthorTypeID (Autonumber)
AuthorType (Author, Illustrator, Editor, etc.)

tblAuthorRank

AuthorRankID (Autonumber)
AuthorRank

I haven't tried to implement this exact structure. In my library
application, I just use the tblAuthorRank, and have fields as follows
(Primary, Secondary, ... , Illustrator). So, I can assign multiple authors
to a book, as well as an illustrator, but it falls apart if I want to assign
"primary authorship" to an "editor". Perhaps in the next re-write of the
app!

Cheers!
Fred Boer




if you want to create a spine label for library shelving, you can use the

 

[Ken Sheridan]

Jimaldo:

That raises an interesting point. What do we mean by a Books entity type?
I'd be inclined to think of it as the book as a 'work', e.g. there might be
hardback and paperback versions of the same book. Also there might be
versions by different publishers, particularly with the 'classics' which are
out of copyright. They are all the same book as a work, so should be
represented by one row in a Books table. Then there is the point you raise
about different editions.

If these issues are going to be covered then it would be necessary to
introduce another table Editions and relate this via an EditionAuthors table
to Authors (whether or not this includes illustrators, differentiated by an
AuthorType attribute in EditionAuthors, or whether it is a (super) type of
Writers and Illustrators entity (sub) types). This would then cater for
different editions of a book having different illustrators.

Incidentally, the reverse can apply and the writer can change while the
illustrator remains the same, which might at first seem to make little sense,
but is actually something of which I have personal experience. My wife was
commissioned a few years ago to write the text for a book of historical
photographs. She appears as the writer on the title page. Later the company
published a pocket edition without my wife's text, but with short captions.
In this her name does not appear on the title page, although there is a
reference to it being based on the larger work by her. It would be
inaccurate to say she was the writer of this pocket edition, however.

Ken Sheridan
Stafford, England

 

[Fred Boer]

Hi, jumping in again...

About a month ago, in this newsgroup, there was an involved thread ("Library
system in access [help and advice plz]), and towards the end of the thread,
John Nurick was outlining "a sort of metabook entity..." - at that point it
seemed wise to me to back away from the issue! <g>

Cheers!
Fred