Results 1 to 5 of 5
  1. #1
    Join Date
    May 2008
    Posts
    3

    Help redesigning a database

    I haven't used Access for more that 5 years and I am rusty. I have inherited a flatfile database with many duplicates. Currently I have about 400 records when I should have only about 140. I want to redesign it. My design problem is that I have husband and wives who may both be members, one a member or neither a member. Each member is given a unique number when they join. The current database lists the woman's first name, last name, address, etc. and then it does the same for the husband, and then it duplicates it creating a third record with the same information for the combined couple so you can send mail to John and Helen Smith, or John Smith and Helen Jones. There might also be grandparents living at the same address creating a 4th record for the same address.l


    TABLE ONE
    I think I need to create a table with a number (duplicates OK) linking to Table Two, for each person. That record would include data such as membership number (if any), date joined, committee they are on etc.

    TABLE TWO would include the address, phone, email, mailing name. and a unique index number that would link this data with table One.

    Does this seem that I am on the right track? This is a church database. Right now I have to change 2 or 3 records when someone moves or changes phone numbers.
    Last edited by ww01810; 05-29-08 at 08:41.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Why are duplicates ok in table one - surely the membership number will be unique?

    I'm assuming in your model that one person only has one registered email address, phone number and address? If so - why are you separating it from the people table?
    George
    Home | Blog

  3. #3
    Join Date
    May 2008
    Posts
    3

    Need Help redesigning a database

    Table Two has the unique number.
    Table One has the linking number that can have duplicates. The husband's record would have the same number as the wife's and both would link to Table 2, the address list.
    Not everyone in the database has a membership number.

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I'd just go for the following tables:

    Code:
    Addresses : id, address
    
    Users : id, first_name, last_name, address_id, phone, 
              email*, membership_no*, date_joined*
    
    Committees : id, name
    
    CommitteePeople : committee_id, user_id, role*
    Option fields are marked with a *. I left the phone number in the User table incase you store mobile numbers - if this isn't a problem then just move the phone field into the Addresses table.

    If people can be on multiple committees then the above works fine but if members are limited to one committee each then you could just have a field called committee in the Users table and forget the CommitteePeople table. I put role into the CommitteePeople table just in case you wanted to know who ran the committee etc.

    just my 2c

    Mike

  5. #5
    Join Date
    May 2008
    Posts
    3

    Help redesigning a database

    Mike,

    Thanks this looks great, it's so clean and simple. I never thought of using more than two tables.

    Hannah

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •