Solved

Database design for Lending Library

Posted on 2009-04-08
6
1,721 Views
Last Modified: 2012-05-06
I am creating a lending libary database - much like in a real library, or movie store.  I am having some issues in constructing the design.  
Subject:   Tools in a company are being checked-out.
Tables
  Tools:
      TID
      TName
      TActive = yes/no
  Supervisor
      SID
      Fname
      Lname

Checked out
       C.O. ID
       SID
       TID
       Date Out
       Date In
I need to create a form that will allow a user to Fill in the following:
Supervisor Name,  Date Checked out,  Date Returned,Tool ID,

The problem is how can i place multiple Tool Ids with one Checkout ID?
Since a person can checkout multiple tools.  
0
Comment
Question by:ansonindustries
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 3

Expert Comment

by:GarthSnyder
ID: 24102791
I'm not sure that I understand your task perfectly, since it sounds like the form you're designing only records the checkout after the fact -- after the tool has circulated and come back, rather than recording the event once at the time of circulation and once at the time of return.

You have the right tables here, but the COID is essentially an artificial primary key and is not in fact necessary. The (ToolID, Date Out) pair is a more natural primary key and will automatically be unique if Date Out includes the time or if tools cannot circulate more than once per day for some reason.

I don't think your question is really about the table schema, though. You are asking how to handle one-to-many relationships in a form, and the answer is that you use a subform. Put the "one" part in the main form (e.g. supervisor) and link those constant fields to a subform that shows the checkout table in datasheet mode. Then you'll be able to enter as many or as few tools as you need for the transaction.
0
 
LVL 23

Expert Comment

by:Eirman
ID: 24102792
You can't beat looking examining a working database similar to your requirements

There is an Access Librarary DB here (Scroll down the page)

http://databases.about.com/od/accessdatabasetemplates/Microsoft_Access_Database_Templates.htm
0
 

Author Comment

by:ansonindustries
ID: 24108184
I have already downloaded the lending library from Access but the problem is that they have 1 to 1 relationships - you can only check out 1 item at a item which can be various tedious.  I might be over thinking this and a subform might be the best option.  just getting stuck on how many columns to allocate for each record on the checkout table.  

I want to create a form that the user will fill out a Date out, Supervisor Name (drop-down) and a date return.  There will be a checkbox for each item that will be taken out.

the form should link to a table resembling the following form.

COID---Supervisor----DateOut----DateIn-----Item1-----item2..........  
Auto#---johndoe-------2/2/09-----2/10/09-----drill------saw-----pump----paintstick.........

I guess how can i keep the flexebility of expansion and how should i track each transaction on a table as multiple items will be taken out by one person in one day and another person in the same day.  keep in mind we will have to add more tools so that means some way of updating the checkbox form automatically.

I hope that gives you guys a better pic.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ansonindustries
ID: 24110998
ok i think im getting closer in coming up with a more solid solution so if you guys can tell me how i can do the following:

form1
  Contains
  CheckoutID - autogen
  Supervisor name: dropdown
  Date Out
  Date In

Form 2 - Subform
  has a listing of all tools that one can take i guess i will have to manually type them each in with a checkbox next to them.

when a user checks the box how can i place the  corresponding value into the table?

[ ]  drill
[/]  hammer
[ ] blade
[/] saw

Table checkout
 ID tool
1   hammer
1   saw
0
 
LVL 3

Accepted Solution

by:
GarthSnyder earned 500 total points
ID: 24111913
I would like to help, but I'm afraid your question is enlarging into something along the lines of "How do I design a database? How do I use Access?" :-) I can only give you a general direction to go in: your initial schema is the right idea. Any time you get into adding table columns for new items or manually updating a form when new tools are added, you know you're barking up the wrong tree.

Pull the list of tools from the tool table and make it a drop-down list just like for supervisors.

I referred to this as a one-to-many relationship, but that's a simplification from the perspective of a particular transaction. From a schema perspective, it's a many-to-many relationship since multiple supervisors can check out multiple tools over time. You MUST use a linking table (the one that pairs supervisor IDs and tool IDs) and subforms for this application; that's the only way it will do what you want and be maintainable.
0
 

Author Closing Comment

by:ansonindustries
ID: 31568301
thanx
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question