How to structure a DB of different booklists

[jds217]

I'm fairly new to Access, and I'm trying to figure out how to structure tables & relationships to accomplish what I want.

I work with the children's dept. of a public library, and we create lots of different book lists which we use to assist patrons. Some books may be on more than one list. We add to these lists regularly, sometimes on the fly as we get new books in, and we may create new lists on the fly as need arises. Some lists will be long -- perhaps a hundred books or more -- and some much shorter.

I know how to create a table for the books (at least I think I do): fields such as bookID, author, title, call number, reading level, etc.

What I'm puzzled about is what kind of table structure would work for the lists of books For example, if I want to create a list of "Pirate Stories", should I create a "Pirate Stories" table, with a single BookID lookup field, to which I can add as many entries as I want?

To further complicate things, I'd like to be able to subdivide my booklists into various categories -- Preschool lists, lists created by a particular staff member, etc.

I'm sure that's not as clear I need to be, but hope someone can help me get handle on this.

Thanks!

John

 

[dhookom]

I would create a table of all the different lists.
[tt][blue]
tblLists
-----------
lisLisID autonumber primary key
lisTitle Title of the list
lisStatus
lisCatID possibly for linking to tblCategories.catCatID
[/blue][/tt]
To add books to lists, you would need a junction table
[tt][blue]
tblBookList
-------------
bliBLiID autonumber primary key
bliBookID links to tblBooks.BookID
bliLisID links to tblLists.lisLisID
[/blue][/tt]
I'm not sure how categories relate to lists and/or books.


Duane
Hook'D on Access
MS Access MVP

 

[bubba100]

Depending on what version of Access you have available, you can Open Access then Create a New Database with Database Wizard. It offers several that might help including Book Collection, Music Collection, Video Collection even one for Reciepes.

 

[SkipVought]

Duane,

Why would you not suggest a single table for all books? Is this not a classic example of a non-normalized design? Or is this an allowable departure, given the two tables you suggested? Then it all hinges on the proper process of adding new tables.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

Skip,
I assumed from the first post that John already had a single table of all books. In my reply, I referenced this table as tblBooks:
[tt][blue]
bliBookID links to tblBooks.BookID
[/blue][/tt]
This is somewhat like my MP3 collection. tblLists contains one record for each different Play List. tblBookList is the junction table that add different tracks to the play list.

I also thought John might want to categorize the play lists.


Duane
Hook'D on Access
MS Access MVP