Results 1 to 7 of 7
  1. #1
    Zoran is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Posts
    7

    Looking for quick opinion on simple database

    I'm trying to create a simple database that keeps track of when members of my school organization pay for their membership as well as their contact info. The options for membership are as follows:

    If paid in the fall semester



    $25 per member without senior status; $40 for two semesters
    $15 per member with senior status: $25 for two semesters
    $50 per member if paid after September 25

    If paid in the spring semester

    $25 per member without senior status
    $15 per member with senior status
    $50 per member if paid after February 5

    As far as I can tell, I only need two tables, which are as follows (Z ID's are unique identifiers given to each student upon enrollment at our college):

    Contact Information
    (Z ID, Last Name, First Name, Address, Address 2, City, State, Zip Code, Email Address)

    Membership Status
    (Payment ID, Z ID, Semester Paid, Expiration Date, Type of Payment, Payment Amount)

    So, for example, if a student has enrolled in our organization this spring his records in the tables would look like:

    (Z123456, Smith, Jon, 1212 Campus Ln, Apt 2, DeKalb, IL, 60115, jsmith@gmailz.com)

    (1, Z123456, Spring 2010, Spring 2010, Check, $25)


    Before I enter all the data, does this look like a feasible design? Thanks in advance.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I would use an AutoNumber for the PrimaryKey of both tables and where are you recording whether they have Senior Status?

  3. #3
    Zoran is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Posts
    7
    Quote Originally Posted by RuralGuy View Post
    I would use an AutoNumber for the PrimaryKey of both tables and where are you recording whether they have Senior Status?
    First off, thanks for the reply!

    The PrimaryKey "Payment ID" is an autonumber. Why should I have it as a PrimaryKey for the contacts table though? I would think it would just be redundant since Z ID's are unique inherently (they're not reused as far as I know).

    Also, the only reason I hesitated to use Senior Status as a field was it would you would to keep track of someone's grade level and update the records every semester. It's a bit of a hassle for the scope of this database.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Considering your answer, what you have done is just fine.

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Since these two tables have a 1:1 relationship why are you using more than one table?

  6. #6
    Zoran is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Posts
    7
    Quote Originally Posted by RuralGuy View Post
    Since these two tables have a 1:1 relationship why are you using more than one table?
    Because one person can have multiple payments. For example, they can pay for the Fall semester in the fall and the Spring semester in the spring (they may have been unsure if they would stay for the second semester which is why they chose toget the discounted membership). Also, you can be a member of the organization for five years which means there is theoretically the chance of having 10 separate payments (two semesters each year).

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    OK, it is a 1:M relationship then. A Form/SubForm will fill in the ForeignKey field for you if you set it up correctly. Be sure and enable Referential Integrity.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Disable/Hide Quick Access Toolbar
    By Jackal in forum Security
    Replies: 10
    Last Post: 11-22-2012, 05:25 PM
  2. Database design for simple inventory
    By toptech in forum Database Design
    Replies: 12
    Last Post: 10-24-2009, 07:24 AM
  3. simple query
    By taylorosso in forum Queries
    Replies: 1
    Last Post: 10-06-2009, 04:26 AM
  4. Very simple question!
    By fiddley in forum Programming
    Replies: 2
    Last Post: 04-28-2009, 02:16 AM
  5. Quick way to stuff field names into text field
    By kfinpgh in forum Programming
    Replies: 0
    Last Post: 01-04-2007, 01:13 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums