locked
Database design strategy question RRS feed

  • Question

  • I am doing some volunteer database design using Access 2010 for a homeless shelter in San Francisco.

    Basically all that will be needed is a Client table with the following fields:

     

    ClientID

    ClientSince (date)

    FirstName

    MI

    LastName

    Address1

    Address2

    City

    State

    ZIP

    IsHomeless (Yes/No)

    IsActive (Yes/No)

    PhoneNumber

    Email

     

    Then there will be a Transaction table TransactionT with the following fields:

     

    TransactionID

    ClientID

    TransactionDate

    ServiceTypeID

    Donation

     

    There is also a table call CodeServiceType

    ServiceTypeID

    ServiceType

    DaysBeforeNextService

    IsActive (Yes/No)

     

    Now the idea here is that clients can only come in for a particular Service once either every 30 days or 90 days, etc. So I need to build a form for entering transactions to record each time a client comes in to receive services. So, for example, clients can only come in and get a bag of groceries every 30 days. If the client tries to come in sooner than that, then the database should pop up a warning message box that says that it is not time yet to receive groceries and prevents the user from entering that service. However, if the user is an Administrator or an Intern, they can override that restriction and issue the groceries anyway.

     

    I can easily build the forms for displaying and editing transactions, but I have never implemented the kind of code or queries that will be required to implement this rule.

     

    As you will see in the structure of the CodeServiceTypeT table, the code or query needs to look at several things. First, they may change the number of days that a client has to wait between getting services (DaysBeforeNextService). Then, it needs to look at if that service type is active. For example, they may not always be able to offer BART transit tickets, so the Admin can turn of, using the IsActive flag when they can't issue them and turn it back on when they can. There will be a combo box on the transaction form for selecting a service. It will only show services that are marked as IsActive = True.

     

    I would appreciate some guidance as to a good strategy for setting this up ? How can I best implement this DaysBeforeNextService Rule to prevent clients from getting the same service twice in the same month ?

     

    Please let me know what you think.

     

    Thanks,


    Steven Schuyler Berkeley, California USA Web Hosting as low as $3.49/month - www.goldengatewebhosting.com

    Friday, April 26, 2013 12:32 AM

Answers

  • Hi Steven

    Your database model seems ok to me.

    I would try to do it in the Form BeforeUpdate event. In there check by VBA code if he can get the service. if not display a MsgBox informing the user that he got it already. Then Cancel the event by setting Cancel = True.

    If the user is an administrator then don't display an information message but a question where the administrator can click on yes. If he didn't click yes, cancel the event.

    What you have to do would be:

    - check if a service code was entered/changed.
    - if this is the case, read with DMax() if and when the last time the user got this service. Then add to this date with dateadd() the days you read out of your service code table with the service code and check if this is greater than today
    - if so, then display the messages as mentioned above, else accept the transaction and write the record to the database.

    HTH

    Henry

    • Marked as answer by Dummy yoyo Monday, May 6, 2013 4:53 AM
    Friday, April 26, 2013 3:14 AM
  • Steven,

    I dont see any "User" Table on your DB design to determine who is an administrator or Intern.  You should have a user table.  If not, have another step where the administrator will have to enter like an special code (to make sure it is an administrator or Intern and overide\allow the transaction) and log it on a separate table for review.  

    • Marked as answer by Dummy yoyo Monday, May 6, 2013 4:54 AM
    Friday, April 26, 2013 11:27 AM

All replies

  • Hi Steven

    Your database model seems ok to me.

    I would try to do it in the Form BeforeUpdate event. In there check by VBA code if he can get the service. if not display a MsgBox informing the user that he got it already. Then Cancel the event by setting Cancel = True.

    If the user is an administrator then don't display an information message but a question where the administrator can click on yes. If he didn't click yes, cancel the event.

    What you have to do would be:

    - check if a service code was entered/changed.
    - if this is the case, read with DMax() if and when the last time the user got this service. Then add to this date with dateadd() the days you read out of your service code table with the service code and check if this is greater than today
    - if so, then display the messages as mentioned above, else accept the transaction and write the record to the database.

    HTH

    Henry

    • Marked as answer by Dummy yoyo Monday, May 6, 2013 4:53 AM
    Friday, April 26, 2013 3:14 AM
  • Steven,

    I dont see any "User" Table on your DB design to determine who is an administrator or Intern.  You should have a user table.  If not, have another step where the administrator will have to enter like an special code (to make sure it is an administrator or Intern and overide\allow the transaction) and log it on a separate table for review.  

    • Marked as answer by Dummy yoyo Monday, May 6, 2013 4:54 AM
    Friday, April 26, 2013 11:27 AM