Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Database design for Lending Library

Posted on 2009-04-08
6
Medium Priority
?
1,912 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
  • 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 24

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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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 1500 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

580 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