Relationships & Primary Key in a Book Inventory Database

[Kris D]

I am trying to create a database for a book program that I run. I am
currently using an excel spreadsheet to track all books in inventory and who
they are checked out to, but I know a database would be a lot easier so that
more than one person can make transactions at the same time.

The problem I have is I don't know what should be set as the primary key,
and how I should se the relationships. I made three tables; Students table
(fields: social, student ID, name, address, phone number), book table
(fields: ISBN number, title, author, category, purchase price, discontinue
date), and transactions table (fields: Book, Checked Out To, Checked Out
Date, Due Date, Checked In Date).

I have several copies on most of the books in inventory with the same ISBN
number, and wasn't sure how to distinguish each book as a different copy and
which field should be a primary key. I thought of usig the ISBN number as a
primary key and at the end of each ISBN number place a dash 01 for the first
copy, dash 02 and so on???

The second problem I am trying to do is relate all three tables together.
One student can check out as many books as they want, so I wasn't sure if
about the one-to many relationships, or one-to-one relationships.

Can someone give me some suggestions on how to create this database?

Thanks

 

[Tom Wickerath]

Hi Kris,

Take a look at this design
http://www.databaseanswers.org/data_models/libraries_and_books/index.htm

It is likely more complicated than what you want or need, but it might give
you some ideas.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Ken Sheridan]

To deal with the question of multiple copies of the same book I'd suggest you
create another table, Copies say, with columns CopyID, ISBN, PurchasePrice
and DiscontinueDate. ISBN is a foreign key column referencing the ISSN
primary key of the Books table. The other columns represent attributes of
each copy of the book, rather than of the book per se. In the jargon they
are said to be functionally dependent on the key of Copies. So if you have
three copies of a book there will be one row for it in Books and three rows
in Copies.

The model will handle a student's checking out multiple books perfectly well
with the addition of a StudentID column to your Transactions table as a
foreign key referencing the primary key of Students, and by changing its Book
column to a CopyID foreign key referencing the primary key of copies. The
table then models the many-to-many relationship between Copies and Students.
So if a student checks out three books there would be three rows inserted
into Transactions, each with the same StudentID and three separate CopyID
values. The book in question is known via the relationship between Copies
and Books.

The model would thus look like this:

Books----<Copies----<Transactions>----Students

with < and > signs indicating the 'many' end of each one-to-many relationship.

The BookID and StudentID primary keys can be autonumber columns. The
corresponding foreign keys in Copies and Transactions should be
straightforward long integer number data types. The CopyID primary key of
Copies can either be an autonumber column with arbitrary values, or could be
a sequential number series starting at 1 for each copy. The former is
obviously easier to implement, but if the latter is used then the CopyID and
ISBN would be the composite primary key of Copies and it would be necessary
to have a corresponding composite two-column foreign key in Transactions. I
see no real advantage in this, so I'd recommend the former simpler option.

Ken Sheridan
Stafford, England

 

[pearl jones]

hi guys, am happy