Relationships & Primary Key in a Book Inventory Database
in [General]
|
From: Kris D Kris on 4 Jul 2008 01:09 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
From: Tom Wickerath AOS168b AT comcast DOT on 4 Jul 2008 03:08 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 __________________________________________ "Kris D" wrote: > 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
From: Ken Sheridan on 4 Jul 2008 13:53 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 "Kris D" wrote: > 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
From: pearl jones on 6 Jul 2008 17:55 hi guys, am happy "Kris D" <Kris D(a)discussions.microsoft.com> ha scritto nel messaggio news:5C2F2DEC-81CC-4C2A-B0EE-804E5A88A6D9(a)microsoft.com... > 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
|
Pages: 1 Prev: specify users in access Next: Access 2007 - Different Ribbons for Different Users? |