Access library database book categories

Question

Hi,

Looking for some help with a library database. My database has a table for books and a table for categories. Each book has more than one category attached to it. How do I create a relationship for these tables? Thanks

Answer 1

You need to set up a many-to-many relationship. This is done by creating a third table that lists the book - category combinations.

See for example Create many-to-many relationships

Answer 2

This is based on personal assessment rather than objective fact.

if under the simple stituation,I would like a table like below.

select * from booklibary;select * from booklibary where categories like '%_1%';

Answer 3

Has Hans replied, you need to create a Junction table that maps the mny to many relationship.

tjxBookCategories

BookCategoryID (PK Autonumber)

BookID (FK)

CategoryID (FK)

Answer 4

You might like to take a look at Library.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

In this little demo file there is a many-to-many relationship type between Publications and Authors, in that each publication can have one or more authors, e.g. A Guide to the SQL Standard (4th edition) has two authors, Chris Date and Hugh Darwin.  The relationship type is modelled by a table Authorship which resolves the relationship type into two one-to-many relationship types:

 

For data input the publications form has an authorship subform in which one or more authors can be entered by selection from a combo box's list.

A many-to-many relationship type between Publications and Categories would be modelled in the same way by a PublicationCategories table, and represented in the publications form by a subform set up in the same way as that for authorship.

Do not be tempted to use a 'multi-valued field' for categories.  This might seem like an easy solution at first sight, but is foreign to the principles of the database relational model, which requires each column position in each row in a table to hold one value only of the attribute represented by that column.  Model the relationship type by a table in the above way, and use a subform as the user interface.

Answer 5

One more example of a valid approach to creating the required junction table for this relational database application.