Database - books in library

[merowing3]

Ave.

I have a question and I would be very grateful for some hints how to do this. I want to create a small and simple database which will consist of entries of books from library (basic information such as: title, names and surnames of authors, edition, year, publisher). I have a problem: let us assume that one book (with one title) have more then one author (for example two).



I tried to create two tables:

1. tblBooks (ID Book, Title, Author, Year, Publisher)
2. tblAuthors (ID Author, Name, Surname)

The problem is that I have only one field Author in tblBooks and if some book have two authors I can choose only one (I can create more then one field Author but when the book will have one author then all other Author fileds will be blank which is not correct). How to solve this problem? I tried to create a third table:

3. tblBooksAuthors

which would connect 1st and 2nd table but it didn't solved my problem. Any clues?

[isladogs]

For your simple system, two tables will be sufficient

Remove Author from tblBooks.
Add BookID to tblAuthors and link the two tables using that.
I advise againsthaving spaces in field names

For more complex databases, your junction table would be used.

[ranman256]

Id say 3 tables,
tBooks would not have an author (string ) field, instead it would have an AuthorGrpID field.

the tAuthorGrp table would have the field AuthorGrpID (long) and AuthorID (long)
so 1 book can have X authors

tAuthors table would have AuthorID (auto) and each persons info.

BookID, Title,AuthorGrp
656, History of Yoyos, 321

tAuthorGrps tbl (*keys)
AuthorGrp*, AuthorID*
321, 2
321, 5

tAuthors tbl
AuthorID, FirstN, LastN
2, Stephen , King
5, Dean , Koonz

[ArviLaanemets]

tBooks would not have an author (string ) field, instead it would have an AuthorGrpID field.

Sorry, but in case AuthorGrpID is PK, then this allows only 1 author per book.

tBooks: BookID, BookName, ...
tAuthors: AuthorID, ForeName, LastName, ...
tAuthorBook; ABID, BookID, AuthorID

[isladogs]

I think the solution depends on the amount of detail the OP wants

If I was doing this for myself, I would definitely want 3 tables for this part of the database plus ISBN codes, additional tables for publisher etc etc
My answer was based purely on what the OP said he wanted

[ranman256]

AuthorGrpID AND AuthorID would be keys, to prevent duplicates.

[orange]

When you are looking ideas regarding a database to support a business, one of the first places to look is Barry Williams' site -- where he has several free generic data models. Many have links to associated facts that led to the design of the model.

These models are meant as a representation of the typical subjects and attributes and relationships. These can be expanded or adjusted to suit your needs. They serve as a starting point to help organize your specific model. You can use parts of the model, ignore parts, revise or extend parts or combine pieces of various models as necessary to satisfy your "business facts".

As he says on the site "these are a kick start of database designs".

He also has tutorials and articles related to data management and database.

Here has a library data model at http://www.databaseanswers.org/data_...alim/index.htm

And a more generic library conceptual model with the associated Business Facts that underlie the model.

Good luck with your project.